Query to count items across multiple columns

Question

I have a table:

ID | fish1 | fish2 | fish3 |
1  | shark | dolfy | whale |
2  | tuna  | shark | dolfy |
3  | dolfy | shark | tuna  |
4  | dolfy | tuna  | shark |

and the result of he query is:

fish  | count |
shark | 4     |
tuna  | 3     |
dolfy | 4     |
whale | 1     |

Can someone give me a proper query for this.

Whales not being fish is the least of what seems amiss here. Looks like a misguided **many-to-many** implementation, with 3 tables at the core: `tbl`, `fish` and `tbl_fish` connecting the first two. The query would be trivial, then. Example implementation: http://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql/9790225#9790225. You could add an attribute `fish_type enum` that can be one of `(fish1, fish2, fish3)` and add a unique or primary constraint over `(tbl_id, fish_id, fish_type)` to limit to 3 fish per entry in `tbl`. — Erwin Brandstetter, Feb 01 '15 at 23:17

score 2 · Accepted Answer · answered Feb 01 '15 at 11:32

You need to create a normalized view on your de-normalized table:

select fish, 
       count(*)
from (
  select fish1 as fish from the_table
  union all
  select fish2 from the_table
  union all
  select fish3 from the table
) t
group by fish

In general it's a bad idea to store your data like that (numbered columns very often indicate a bad design).

You should rather think about a proper one-to-many relationship.

score 0 · Answer 2 · answered Feb 01 '15 at 11:33

0

select fish, count (id)
from
(
SELECT fish1 as fish, id from my_table
union all
SELECT fish2 as fish, id from my_table
union all
SELECT fish3 as fish, id from my_table
) A
group by fish

answered Feb 01 '15 at 11:33

Houari

5,326
3
31
54

Which link are you talking about ? – Houari Feb 01 '15 at 15:34

score 0 · Answer 3 · answered Feb 01 '15 at 11:35

Another of doing it

select 'shark' as fish, count(case when fish1='shark' then 1 end)+count(case when fish2='shark' then 1 end)+
        count(case when fish3='shark' then 1 end) count from yourtable
union all
select 'tuna', count(case when fish1='tuna' then 1 end)+count(case when fish2='tuna' then 1 end)+
        count(case when fish3='tuna' then 1 end) from yourtable
union all
select 'dolfy', count(case when fish1='dolfy' then 1 end)+count(case when fish2='dolfy' then 1 end)+
        count(case when fish3='dolfy' then 1 end) from yourtable
union all
select 'whale',count(case when fish1='whale' then 1 end)+count(case when fish2='whale' then 1 end)+
        count(case when fish3='whale' then 1 end) from yourtable

This has the huge disadvantage that you need to adjust the query each time you add a new fish type. — , Feb 01 '15 at 11:45

Query to count items across multiple columns

3 Answers3