Count multiple results in MySQL query

Question

Basically what I have is a ratings system. 1-5 stars for 4 categories. What I'm trying to do is get a count of the results for each category. For instance:

  Category 1: 20 1 star, 32 2 star, 40 3 star, 35 4 star, 19 5 star
  ...
  Category 4: 10 1 star, 12 2 star, 45 3 star, 54 4 star, 2 5 star

I know that I can just do 20 queries, (SELECT COUNT(*) WHERE BizID=$id AND category1=1 / SELECT COUNT(*) WHERE BizID=$id AND category1=2...), but I was wondering if there was a more efficient way. Seems like if I had a large number of hits, or there were a huge number of ratings, the script would bog down pretty bad at this point. Any help would be appreciated.

Table Structure
RateID - int(10)
UserID - varchar(8)
BizID - varchar(8)
Cat1 - int(1)
Cat2 - int(1)
Cat3 - int(1)
Cat4 - int(1)
Date - int(10)

You can use IN for this.

prava
– prava

2014-07-11 06:22:41 +00:00
Commented Jul 11, 2014 at 6:22 — prava
– prava, Commented Jul 11, 2014 at 6:22

Joseph B · Accepted Answer · 2014-07-11 06:24:13Z

1

You can get counts by Category and the Star rating in one shot using the CASE statement, as below:

SELECT
    SUM
    (
        CASE
            WHEN Cat1 = 1 THEN 1
            ELSE 0
        END
    ) Cat1_1,
    SUM
    (
        CASE
            WHEN Cat1 = 2 THEN 1
            ELSE 0
        END
    ) Cat1_2,
    SUM
    (
        CASE
            WHEN Cat1 = 3 THEN 1
            ELSE 0
        END
    ) Cat1_3,
    SUM
    (
        CASE
            WHEN Cat1 = 4 THEN 1
            ELSE 0
        END
    ) Cat1_4,
    SUM
    (
        CASE
            WHEN Cat1 = 5 THEN 1
            ELSE 0
        END
    ) Cat1_5,
    SUM
    (
        CASE
            WHEN Cat2 = 1 THEN 1
            ELSE 0
        END
    ) Cat2_1,
...
...
... 
FROM mytable
WHERE BizID=$id;

answered Jul 11, 2014 at 6:24

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

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

Comments

prava · Accepted Answer · 2014-07-11 06:23:36Z

0

Try with this:

<?php
$sql = "SELECT COUNT(BizID) WHERE BizID = " . $id . " AND category1 IN (1, 2, 3, 4, ...)";

answered Jul 11, 2014 at 6:23

prava

3,9762 gold badges26 silver badges35 bronze badges

2 Comments

prava Over a year ago

@LinkinTED, not understood. Please write in details.

tom redfern Over a year ago

The question is not how to do it but how to do it better.

Collectives™ on Stack Overflow

Count multiple results in MySQL query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related