Need to count most entered entries from multiple columns...

3dron

Imagine a database with one column for name, and 6 more columns or picking 5 favorite foods:

Now how can I do a count of all pick columns considered as one. Then just be able to get output like this:

Pizza 4
Sushi 3
Burger 2
Pasta 2

johanafm

SELECT pick, sum(amt) FROM
(SELECT Pick1 AS pick, count(Pick1) AS amt FROM tbl GROUP BY Pick1
UNION ALL
SELECT Pick2, count(Pick2) FROM tbl GROUP BY Pick2
) AS t
GROUP BY pick;

3dron

I know I am dense but how do I add the additional columns? Right now your script only checks first two pick columns. And in fact my real database has 20 pick columns (pick1 thru pick20), so I hope a nest select statement doesnt have to be create for each column.

thanks Ron

3dron

Is this correct???

SELECT pick, sum(amt) FROM
(SELECT pick1 AS pick, count(pick1) AS amt FROM food GROUP BY pick1
UNION ALL
SELECT pick2, count(pick2) FROM food GROUP BY Pick2
UNION ALL
SELECT pick3, count(pick3) FROM food GROUP BY Pick3
UNION ALL
SELECT pick4, count(pick4) FROM food GROUP BY Pick4
UNION ALL
SELECT pick5, count(pick5) FROM food GROUP BY Pick5
) AS t
GROUP BY pick ORDER BY sum(amt) DESC

halojoy

I think you can consider to arrange tables different.

Table 'users'
userid username
1 John
2 Laura

Table 'food'
id food picknum userid
1 pizza 1 1
2 burger 2 1
3 lobster 1 2
4 pasta 2 2
5 pizza 3 2

djjjozsi

This database design should normalized.
names table:
nameid , name

pick table:
pickid , pickname

You should make a new table, called connector_names_pick

add two fields into this table (integer types):
names_id And pick_id

Means names_id will be equal with names's nameid field, and pick_id will be equal with the pick table's pickid.

Each matches between names and picks stored in new lines.
example:
names table:

1  john
2 peter

pick table:

5  apple
6  brush

connector_names_pick table:

1 5
1 6
2 5

jay6390

djjjozsi has what you need. This is the method you need to use
Then its just a case of using a query like

SELECT`pick`.`pickname`, COUNT(`pick_id`) AS `total` FROM `connector_names_pick`, `pick` WHERE `pick`.`pickid` = `pick_id` GROUP BY `pick_id`

djjjozsi

in that case a 'pick' hasn't been selected then a (LEFT/RIGHT) JOIN would be a better choice.

Select p.pickid, p.pickname, Count(cc.names_id)
From connector_names_pick cc Right Join
  pick p On p.pickid = cc.pick_id
Group By p.pickid, p.pickname
Order By Count(cc.names_id) Desc

jay6390

Yup it would