mysql count function problems

Question

I have a table which is formatted as:

id |  food  | userID | beerID

I am trying to select all the foods for a given beerID and a count of repeats. SO if there are 3 entries for pizza I get results like:

food      | beerID | count

pizza     | 34     | 2
hot wings | 34     | 1
pasta     | 34     | 5

Does this work? I am a bit confused on using the count with the group by.

select food, beerID , count() where beerID = 34 group by food

General note about GROUP BY: You should include in the GROUP BY clause all of the columns in the SELECT clause (and vice versa), the exception being any aggregate functions being applied to each group (such as COUNT(*)). — Turophile
– Turophile, Commented May 12, 2014 at 1:24

Sterling Archer · Accepted Answer · 2014-05-12 01:13:54Z

2

Give the count a parameter.

select food, beerID, count(food) from tablename as num where beerID = 34 group by food;

Footnote: "does this work" is a bad question. You'll 100% chance be rebutted with "did you not try it..?"

As noted in the comments you aren't selecting from a specific table, so it will error out

answered May 12, 2014 at 1:07

Sterling Archer

22.5k19 gold badges86 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wow totally missed that

Jason Aller · Accepted Answer · 2014-05-12 01:52:11Z

0

SELECT food, beerID, count(1) as beerCount
    FROM tablename
    WHERE beerID=34
    GROUP BY food;

Jason Aller

3,66028 gold badges43 silver badges40 bronze badges

answered May 12, 2014 at 1:07

Josh from Qaribou

6,9182 gold badges25 silver badges21 bronze badges