SQL SELECT same column different Counts

[stanl]

I have an Access table with Date, Name, Stuff - which now has over 40,000 rows across several weeks with multiple names. The Stuff column holds either a positive/0 or negative number.


I can query SELECT Date, Name, Count(Stuff) as [Good Stuff] WHERE Stuff>=0 GROUP BY Date,Name


and SELECT Date, Name, Count(Stuff) as [Bad Stuff] WHERE Stuff<0 GROUP BY Date,Name


But I want to combine them as a single query to display Date,Name,[Good Stuff],[Bad Stuff]

Doesn't seem to want to play well with UNION, groups every thing into [Good Stuff]. I tried a subselect and keep getting "Must include WHERE EXISTS in Query" message. If I can get it to a single row I want to expand to an inner join with the Name column and another table to add more columns.


I'll admit, I've been away from more advanced SQL for a while and a bit rusty, so would appreciate some pointers.

[Bob Phillips]

Try this Stan (I had to change the names)


SELECT StuffDate, StuffName,
(SELECT COUNT(Stuff)
FROM MyStuff AS Good
WHERE Good.StuffDate = AllStuff.StuffDate AND
Good.StuffName = AllStuff.StuffName AND
Good.Stuff >=0) AS [Good Stuff],
(SELECT COUNT(Stuff)
FROM MyStuff AS Bad
WHERE Bad.StuffDate = AllStuff.StuffDate AND
Bad.StuffName = AllStuff.StuffName AND
Bad.Stuff <0) AS [Bad Stuff]
FROM MySTuff AS AllStuff
GROUP BY StuffDate, StuffName

[stanl]

Yeah, I ended up with something similar (with join added)


SELECT A.Date, S.center, S.NAME, S.State, S.Zip, Count(A.Stuff) AS [Good], (SELECT Count(A1.Stuff) FROM
MyTable AS A1 WHERE A1.EID = A.EID AND A1.Date=#|t2|# AND A1.Stuff<0) AS [Bad]
FROM MyTable AS A INNER JOIN MoreStuff AS S ON A.ID = S.ID
WHERE (((A.Date)=#|t2|#) AND ((A.Stuff)>=0))
GROUP BY A.Date, A.ID, S.center, S.NAME, S.State, S.Zip;


However, the time it takes to run two individual queries for good and bad, then a third query joining the 2 is about 1/4 the time it takes to run the subselect. Guess sometimes it pays to stick to basics