How to use SUM and COUNT mysql in one query

Question

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?

Hossam · Accepted Answer · 2017-05-11 23:43:22Z

5

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

answered May 11, 2017 at 23:43

Hossam

1,1469 silver badges19 bronze badges

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

1 Comment

sumit sharma Over a year ago

above query more optimzed as compare to without GROUP BY query

Ramu Manavalan · Accepted Answer · 2017-05-11 23:50:07Z

1

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

answered May 11, 2017 at 23:50

Ramu Manavalan

511 silver badge3 bronze badges

Comments

kojow7 · Accepted Answer · 2017-05-11 23:48:51Z

0

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.

edited May 11, 2017 at 23:48

answered May 11, 2017 at 23:43

kojow7

11.5k21 gold badges94 silver badges148 bronze badges

Comments

Dom DaFonte · Accepted Answer · 2017-05-11 23:56:44Z

0

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');

answered May 11, 2017 at 23:56

Dom DaFonte

1,7912 gold badges18 silver badges34 bronze badges

Comments

James · Accepted Answer · 2017-05-11 23:49:23Z

-1

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

edited May 11, 2017 at 23:49

answered May 11, 2017 at 23:43

James

93311 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to use SUM and COUNT mysql in one query

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related