By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,523 Online
Bytes IT Community
+ Ask a Question
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

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
  1. SELECT cat,sum(Abs(cat="A" or "B" or "C" or "D")) as Cat_Tot
  2. from sample
  3. 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
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,923
try it this way:
Expand|Select|Wrap|Line Numbers
  1. SELECT cat,sum(Abs(cat="A" or cat="B" or cat="C" or cat="D")) as Cat_Tot
  2. from sample
  3. group by cat
if the above does not work, try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT cat,sum(Abs(IIf(cat="A" or cat="B" or cat="C" or cat="D"))) as Cat_Tot
  2. from sample
  3. 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
  1. SELECT Letter
  2.        Sum(Cats) AS NoCats,
  3.        Sum(Rats) AS NoRats,
  4.        Sum(Rabbits) AS NoRabbits
  6. FROM (SELECT tL.Letter,
  7.              Count(tL.Letter) AS Cats,
  8.              0 AS Rats,
  9.              0 AS Rabbits
  11.       FROM [tblLetters] AS tL INNER JOIN [Sample] AS tS
  12.         ON tL.Letter=tS.Cat
  14.       GROUP BY tL.Letter
  16.       UNION ALL
  17.       SELECT tL.Letter,
  18.              0 AS Cats,
  19.              Count(tL.Letter) AS Rats,
  20.              0 AS Rabbits
  22.       FROM [tblLetters] AS tL INNER JOIN [Sample] AS tS
  23.         ON tL.Letter=tS.Rat
  25.       GROUP BY tL.Letter
  27.       UNION ALL
  28.       SELECT tL.Letter,
  29.              0 AS Cats,
  30.              0 AS Rats,
  31.              Count(tL.Letter) AS Rabbits
  33.       FROM [tblLetters] AS tL INNER JOIN [Sample] AS tS
  34.         ON tL.Letter=tS.Rabbits
  36.       GROUP BY tL.Letter
  38.       ORDER BY tL.Letter) AS subQ
  40. 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

Post your reply

Sign in to post your reply or Sign up for a free account.