CODEWITHSUNDEEP
mysql
Cesar Bielich
Cesar Bielich

Reputation: 4945

How to use SUM and COUNT mysql in one query

Not sure if this is possible but I have the following

SELECT SUM(rating) as rating FROM details WHERE client_id = '$id'

The rating column contains individual ratings of users (1,2,3,4,5) stars. I need to sum them all to be able to calculate the average, but I would also like to get how many stars of each delimiter that user got as well.

For instance

The user has 3 records and the ratings are (3,4,3). With a SUM of 10 out of 3 records, I get 3.3 average. But I would like as well

3 stars = 2
4 stars = 1

Is it possible to do this with one query?

Upvotes: 1

Views: 1963

Answers (5)

Dom DaFonte
Dom DaFonte

Reputation: 1779

You don't need to sum your rating. I believe what you want is to key off the rating. I'd suggest this.

This will group every rating then tell you how many times this client is rated something that many times. This would also help you account for any instances where they made a decimal vote (i.e. 4.5 stars) 

SELECT 
    concat(rating, ' Stars') as rating, 
    count(*) as count
    FROM details 
        WHERE client_id = '$id'
    Group by  concat(rating, ' Stars');

Upvotes: 0

Ramu Manavalan
Ramu Manavalan

Reputation: 51

SELECT client_id ,sum(if(rating='1',1,0)) onestar,sum(if(rating='2',1,0)) twostar,sum(if(rating='3',1,0)) threestar,sum(if(rating='4',1,0)) fourstar,sum(if(rating='5',1,0)) fivestar, SUM(rating) sumrate,AVG(rating) as avgrate FROM details WHERE client_id = '$id'

or for all clients

SELECT client_id ,sum(if(rating='1',1,0)) onestar,sum(if(rating='2',1,0)) twostar,sum(if(rating='3',1,0)) threestar,sum(if(rating='4',1,0)) fourstar,sum(if(rating='5',1,0)) fivestar, SUM(rating) sumrate,AVG(rating) as avgrate FROM details WHERE group by client_id

Upvotes: 1

James
James

Reputation: 923

I think this should do it. Also, you said you're summing them only to take the average. Why not use AVG() instead.

SELECT COUNT(rating), AVG(rating) as rating FROM details WHERE client_id = '$id' GROUP BY rating

Upvotes: -1

kojow7
kojow7

Reputation: 11384

It is a bit unclear what exactly you are after, but this might work:

SELECT rating, count(rating) FROM details WHERE client_id = '$id' GROUP BY rating;

Also, not sure how you are getting $id, but if you are getting it from the user, you should be using prepared statements rather than inserting it directly in your SQL code.

Upvotes: 0

Hossam
Hossam

Reputation: 1146

If I got your question right, you can use AVG() function instead of SUM().

In the following query, it calculates the average rating of this client_id, also counts number of each star as required in question.

SELECT
    AVG(rating) as rating,
    COUNT(CASE WHEN rating=1 THEN 1 END) as star_1,
    COUNT(CASE WHEN rating=2 THEN 1 END) as star_2,
    COUNT(CASE WHEN rating=3 THEN 1 END) as star_3,
    COUNT(CASE WHEN rating=4 THEN 1 END) as star_4,
    COUNT(CASE WHEN rating=5 THEN 1 END) as star_5
FROM
    details
WHERE
    client_id = ID_HERE
GROUP BY
    client_id

Upvotes: 5

Related Questions