'Query Creation - Do I use SUM, Group etc.?

I'm confused on how I could create the query in relation to counting rows.

Here is what my data looks like in the DB:

http://i.stack.imgur.com/CgeAV.png

I would like it to give me a total for each username where item = 1 and size = 3.

How would I do that? I've tried:

SELECT Username, SUM(size=3 AND item=1) as LargePopcornTotal FROM statistics2 GROUP BY Username;

I've tried IF statements with SUM and without SUM etc.

mysql


Solution 1:[1]

You're on the right lines, but you should limit in the WHERE clause, not in the sum. The sum is just used to "add" the results from all the grouped rows together.

SELECT
    username,
    SUM(quantity) as LargePopcornTotal
FROM
    statistics2
WHERE
    size = 3
    AND item = 1
GROUP BY
    username

Solution 2:[2]

select Username, sum(quantity) as LargePopcornTotal
from statistics2
where item = 1
and size =  3
group by username

Solution 3:[3]

try this

select count(*) from stats where item = 1 and size = 3

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jon Story
Solution 2 SMA
Solution 3 Matt

Related Questions