问题描述:

I have a table of information with multiple columns but each column can hold only 1 of 3 values (0,1,2). It is generated through user inputted choices of Yes, No Maybe. I want to count each column and be fed back the values.

EG Table:

ID Coffee Tea Water Hot_Choc

1 1 0 2 2

2 0 2 0 1

3 1 2 0 2

4 2 0 1 1

5 1 1 2 2

6 0 1 2 1

7 2 1 0 1

8 1 2 1 2

I'd like to query the data to an output like this:

 Coffee Tea Water Hot_Choc

0 2 2 3 0

1 4 3 2 4

2 2 3 3 4

I tried running a basic count script:

SELECT coffee, count(*) FROM Drinks GROUP BY coffee

Which works fine.

and then tried evolving into:

SELECT coffee,tea,water,hot_choc, count(*) from Drinks group by coffee,tea,water,hot_choc

But with that I get a count for each instance. So when coffee is 0 what are the counts for tea, water and hot_choc.

I also tried with a sum(iif clause because I'm running in access:

select sum(iif(coffee=0,1,0)) as coffee_maybe,

sum(iif(coffee=1,1,0)) as coffee_no,

sum(iif(coffee=2,1,0)) as coffee_yes from drinks;

etc.

Which gets me this:

Coffee_maybe coffee_no coffee_yes tea_maybe tea_no tea_yes etc...

2 4 2 2 3 2

So I'm wondering if anyone has other thoughts as to how to get the count in the above format.

Thanks so much for the help, sorry it's been a long read I wanted to give as much context as possible.

网友答案:

I think the following will work in Access:

select 0, sum(iif(coffee = 0, 1, 0)) as coffee, sum(iif(tea = 0, 1, 0)) as tea,
       sum(iif(water = 0, 1, 0)) as water, sum(iif(hot_choc = 0, 1, 0)) as hot_choc
from drinks d
union all
select 1, sum(iif(coffee = 1, 1, 0)) as coffee, sum(iif(tea = 1, 1, 0)) as tea,
       sum(iif(water = 1, 1, 0)) as water, sum(iif(hot_choc = 1, 1, 0)) as hot_choc
from drinks d
union all
select 2, sum(iif(coffee = 2, 1, 0)) as coffee, sum(iif(tea = 2, 1, 0)) as tea,
       sum(iif(water = 2, 1, 0)) as water, sum(iif(hot_choc = 2, 1, 0)) as hot_choc
from drinks d;

There are slightly easier ways to do this in other databases.

相关阅读:
Top