SQL sub-query problem
-
Dear all,
I now have a problem for the my sub-query, my SQL code is as below:-
SELECT COUNT(t1.StudentID), COUNT(t2.StudentID)
FROM
(SELECT StudentID
FROM Attendance
WHERE ClassId=1 and Month(Date)=11 and status=0
GROUP BY studentID
HAVING COUNT(studentid)<3 ) T1,
(SELECT StudentID
FROM Attendance
WHERE ClassId=1 and MONTH(Date)=11 and status=0
GROUP BY studentID
HAVING COUNT(studentid)>2 AND COUNT(studentid)<6 ) T2
The problem here is that the results are 0,0 for both counts. This is not what i was expecting, the answer should be 3 for t1's count and 0 for t2's count. However, if i run the SQL like below:-
SELECT COUNT(t1.StudentID)
FROM
(SELECT StudentID
FROM Attendance
WHERE ClassId=1 and Month(Date)=11 and status=0
GROUP BY studentID
HAVING COUNT(studentid)<3 ) T1
The count returns 3 as expected but i just don't get it, why does the second SQL query works while the first one does not? Please help me out here and do point out if I am doing anything wrong.
Thanks.
-
If you put the COUNTs in the subqueries, I think that this will work:
SELECT t1.TotalRows, t2.TotalRows FROM
(SELECT count(StudentID) TotalRows
FROM Attendance
WHERE ClassId=1 and Month(Date)=11 and status=0
GROUP BY studentID
HAVING COUNT(studentid)<3 ) T1,
...
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi there Phil,
I tried out your suggestion and it won't work. Besides that, it won't give me the results I want because the having clause filters whatever is returned by the SELECT query (if I am right).
Anybody have any idea why this my problem is happening, or any other solution?
Thanks.
-
OK - I see now what you're trying to do.
One idea I've had, which I've not tested, is for you to have a single subquery containing two counts
select studentid, count1, count2 (this is pseudo-code)
and change your WHERE clause to include the data from both your subqueries:
HAVING COUNT(studentid) < 6
then use a CASE statement to assign the count to either count1 or count2. Does that make sense?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I get what you are trying to do but unfortunately I am not very good in SQL Server's syntax and I have no idea on how to put a CASE statement after my selection.
Could you be kind enough to show me how? Or provide a sample on how to do it?
Thanks.
-
OK, here's an example. Try running against the Northwind database.
select employeeid,
Count1 = (
case
when Count(EmployeeID) < 100 then Count(EmployeeID)
else 0
end
),
Count2 = (
case
when Count(EmployeeID) >= 100 then Count(EmployeeID)
else 0
end
)
from orders
group by EmployeeID
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I see where we are heading here, and i have tried out the code you gave on the NorthWind database. Now I want to sum COUNT1 and COUNT2, but when i put SUM( COUNT1=(....) ) I get an error that says there is a syntax error near '='.
How would I go about to sum them up? Thanks for the prompt reply, really appreciate your help and your time spent.
-
Sticking with Northwind, is this roughly where you're heading?
select sum(t1.Count1), sum(t1.Count2) from
(select employeeid,
Count1 = (
case
when Count(EmployeeID) < 100 then Count(EmployeeID)
else 0
end
),
Count2 = (
case
when Count(EmployeeID) >= 100 then Count(EmployeeID)
else 0
end
)
from orders
group by EmployeeID) t1
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Alright, I see where my error was when I adapted the code to fit my needs. It's all fine now and everything is working as it should
Thanks Phil, you're a real life-saver. Thanks again.
-
Been playing around with the SQL above.
The reason the original SQL returns 0, 0 is because hidden in the query is a cross join where one of the queries returns no rows. A cross join multiplies up the number of rows; if one query is empty, the resultset is empty too.
Using an adaptation of the original query to Northwind:
SELECT COUNT(t1.EmployeeID), COUNT(t2.EmployeeID)
FROM
(SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)<100 ) T1, -- returns 5 rows
(SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)>100 ) T2 -- returns 4 rows
returns 20, 20
-- No claims this is 'better' than the query above
-- simply an illustration of another way to
-- remove the fur from a feline
Select (Select count(*)from (select employeeid from Orders group by employeeId having count(*)<100) t1) countT1,
(Select count(*)from (select employeeid from Orders group by employeeId having count(*)>100) t1) countT2
This query returns 5, 4
Regards
Otto
Best Regards,Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke) -
The problem here is that the results are 0,0 for both counts. IT mean 3 * 0 = 0
SELECT COUNT(t2.StudentID)
FROM
(SELECT StudentID
FROM Attendance
WHERE ClassId=1 and MONTH(Date)=11 and status=0
GROUP BY studentID
HAVING COUNT(studentid)>2 AND COUNT(studentid)<6 ) T2
return 0
-
Let me try to explain what's going on in a little more detail.
Using the northwind database query
SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
returns 9 rows (Employee ids 1, 2, ... 8, 9)
Query
SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)<100
returns 5 rows (Employee ids 2, 5, 6, 7 and 9)
Query
SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)>100
returns 4 rows (Employee ids 1, 3, 4 and 8)
Query
SELECT COUNT(t1.EmployeeID), COUNT(t2.EmployeeID)
FROM
(SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)<100 ) T1, -- returns 5 rows
(SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)>100 ) T2 -- returns 4 rows
returns 20 rows; the pairs
2, 1
5, 1
6, 1
7, 1
9, 1
2, 3
5, 3
6, 3
7, 3
9, 3
2, 4
5, 4
6, 4
7, 4
9, 4
2, 8
5, 8
6, 8
7, 8
9, 8
because in the cross join every row in the first resultset is combined with every row in the second.
the using the count clause will return 20, 20 as there are 20 non-null values for each count.
If you change the selections to
SELECT *
FROM
(SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)<1000 ) T1, -- returns 9 rows
(SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(EmployeeID)>1000 ) T2 -- returns 0 rows
In this case, the rowset resulting from combining the two subqueries is empty, and the counts will be 0, 0.
In summary:
1. the query contains a cross join - (think this is also called a cartesian join), therefore the resulting rowset contains a number of rows which is the product of the number of rows in the subqueries. If one subquery is empty, the resultset will also be empty.
2. the count clause operates on all the rows in the combined resultsset. it doesn't take account of the different number of rows in the subqueries; it operates on the combined rowset.
Hope this helps explain what's going on.
Regards
Otto
Best Regards,Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply