454,224 Members | 1,523 Online
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

# Counting rows

 P: 2 Hello, I have a table with the below data: ID Cat Rat Rabbits 001 A B D 002 A C E 003 B A A 004 A E B 005 D A B 006 B C A I want to count the # of A,B,C,D and E's from each column. Expand|Select|Wrap|Line Numbers SELECT cat,sum(Abs(cat="A" or "B" or "C" or "D")) as Cat_Tot from sample group by cat The above query works for single column. Output sample. Cat Count Rat Count Rabbits Count A 2 A 2 A 1 B 2 B 1 C 2 Can anyone help with this please? Oct 10 '08 #1
4 Replies

 Expert 100+ P: 1,923 try it this way: Expand|Select|Wrap|Line Numbers SELECT cat,sum(Abs(cat="A" or cat="B" or cat="C" or cat="D")) as Cat_Tot from sample group by cat if the above does not work, try this: Expand|Select|Wrap|Line Numbers SELECT cat,sum(Abs(IIf(cat="A" or cat="B" or cat="C" or cat="D"))) as Cat_Tot from sample group by cat Oct 12 '08 #2

 Expert Mod 15k+ P: 31,707 I can't believe this is possible to achieve in SQL (without breaking it up into three different queries and UNIONing the results together). How can it be when you'd need to GROUP BY all three columns independently. I will look into a solution for you. Oct 12 '08 #3

 Expert Mod 15k+ P: 31,707 This will be complicated anyway. Do you have a table which contains all the possible "letter"s that you want to report on? Without this the resulting SQL will be quite extraordinarily complex. Oct 12 '08 #4

 Expert Mod 15k+ P: 31,707 Assuming there is such a table ([tblLetters] with field [Letter]) then try : Expand|Select|Wrap|Line Numbers SELECT Letter        Sum(Cats) AS NoCats,        Sum(Rats) AS NoRats,        Sum(Rabbits) AS NoRabbits   FROM (SELECT tL.Letter,              Count(tL.Letter) AS Cats,              0 AS Rats,              0 AS Rabbits         FROM [tblLetters] AS tL INNER JOIN [Sample] AS tS         ON tL.Letter=tS.Cat         GROUP BY tL.Letter         UNION ALL       SELECT tL.Letter,              0 AS Cats,              Count(tL.Letter) AS Rats,              0 AS Rabbits         FROM [tblLetters] AS tL INNER JOIN [Sample] AS tS         ON tL.Letter=tS.Rat         GROUP BY tL.Letter         UNION ALL       SELECT tL.Letter,              0 AS Cats,              0 AS Rats,              Count(tL.Letter) AS Rabbits         FROM [tblLetters] AS tL INNER JOIN [Sample] AS tS         ON tL.Letter=tS.Rabbits         GROUP BY tL.Letter         ORDER BY tL.Letter) AS subQ   GROUP BY [Letter] You can probably see that doing this without a table to use as [tblLetters] would be somewhat convoluted. Oct 12 '08 #5