Counting rows

[anishap]

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.

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?

[puppydogbuddy]

try it this way:

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:

SELECT cat,sum(Abs(IIf(cat="A" or cat="B" or cat="C" or cat="D"))) as Cat_Tot
from sample
group by cat

[NeoPa]

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.

[NeoPa]

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.

[NeoPa]

Assuming there is such a table ([tblLetters] with field [Letter]) then try :

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.