I am new to correlated subqueries, but have spent a couple hours scouring the
web for any information why this query doesn't work to no avail. Everything I
have read suggests that I am doing everything correctly, yet I receive an error
stating:
SQL error: no such column: t1.myColumn1
Does anyone know why this isn't working in SQLite3.2.1?
SELECT t1.myColumn1, query1.column1
FROM myTable AS t1,
( SELECT count(1) AS column1
FROM myTable AS t2
WHERE t1.myColumn1=t2.myColumn1
GROUP BY myColumn2
ORDER BY column1
DESC LIMIT 1
) AS query1
GROUP BY t1.myColumn1
ORDER BY count(1)
DESC LIMIT 10;
I have one table that has 2 columns, myColumn1 and myColumn2. I want to create
a result set that is grouped by myColumn1, and lists a count of the most times
something in myColumn2 occurs for each of myColumn1.
myColumn1 | myColumn2
1 | a
1 | a
1 | b
1 | a
2 | b
2 | c
2 | a
3 | c
3 | c
The result would be:
1 | 3
2 | 1
3 | 2
because 'a' occured three times in the set of 1s, each letter only occured once
in the set of 2s, and c occured twice in the set of 3s.
The statement works fine if I replace this line:
WHERE t1.myColumn1=t2.myColumn1
with this line:
WHERE t2.myColumn1 = 1
Except it returns:
1 | 3
2 | 3
3 | 3
as it should. This indicates to me that it's a problem with my correlation, but
I don't know what. Any help would be appriciated!
