SQL Query count rows where multiple column-values

Question

So this is a how my MySQL table looks like (I have many thousands of rows):

| ID |         date        |   Color |   IUSERQ | 
|  1 | 2020-09-25 18:55:54 |    RED  |    GGW3  |
|  2 | 2020-09-25 18:24:12 |    RED  |    FFQ3  |
|  3 | 2020-09-24 17:32:52 |    RED  |    GWW3  |
|  4 | 2020-09-23 17:42:37 |   BLUE  |    JJN6  |
|  5 | 2020-09-23 17:33:55 |   BLUE  |    VVV5  |
|  6 | 2020-09-23 18:53:57 |    RED  |    FFQ3  |
|  7 | 2020-09-22 18:15:11 |   BLUE  |    FFQ3  |

Now to count all of the rows and group them in weeks, I do this:

if($stmt = $link->query("SELECT WEEK(date),COUNT(*) FROM sales WHERE color='RED' AND YEAR(date) = YEAR(NOW()) GROUP BY WEEK(date) order by MONTH(date) ASC")){

$php_data_array = Array(); // create PHP array
while ($row = $stmt->fetch_row()) {
   $php_data_array[] = $row; // Adding to array
   }
}else{
echo $link->error;
}

echo json_encode($php_data_array);

On the echo json_encode($php_data_array);, it gives the this current output: [["36","154"],["37","247"],["38","275"]]. So the first string in the array (36, 37, 38) is the week number, and the second one the number of rows where color is RED. Now, I also want to add where color is BLUE in the same array, so the expected value should be something like: [["36","154","166"],["37","247","265"],["38","275","298"]].

What approach should I use to do this?

...although I'm struggling to see how the data set matches up with the result set — Strawberry
– Strawberry, Commented Sep 26, 2020 at 17:25
@Strawberry How are you struggling? I basically copy/pasted from my project. — Erik Auranaune
– Erik Auranaune, Commented Sep 26, 2020 at 23:27

Gordon Linoff · Accepted Answer · 2020-09-25 22:52:39Z

1

Just add another column:

SELECT WEEK(date) as week, SUM(color = 'RED') as red, SUM(color = 'BLUE') as blue
FROM sales
WHERE color IN ('RED', 'BLUE') AND YEAR(date) = YEAR(NOW())
GROUP BY WEEK(date)
ORDER BY WEEK(date) ASC;

Note the changes to the query:

The SUM() does conditional aggregation for each color.
The columns are given names, which can be used to reference them in the application.
The ORDER BY is by the column being aggregated.

answered Sep 25, 2020 at 22:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

GMB · Accepted Answer · 2020-09-25 22:51:48Z

0

Use conditional aggregation:

select week(date) week_date, sum(color = 'RED') cnt_red, sum(color = 'BLUE') cnt_blue
from sales 
where color in ('RED', 'BLUE') and date >= date_format(current_date, '%Y-%m-01')
group by week(date) 
order by week_date

Note that this uses direct filtering on the date column, without applying functions on the filtered column. This is more efficient that year(date) = year(now()), especially if you have an index on that column.

I also changed the order by clause: obviously, you want to order by week rather than month (in most databases, that's a compilation error, because the month does not belong to the group by clause).

answered Sep 25, 2020 at 22:51

GMB

224k25 gold badges103 silver badges151 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2020-09-26 12:53:52Z

0

You can use dynamic query as another option :

SET @sql = NULL;

SELECT GROUP_CONCAT(
             DISTINCT
             CONCAT(
                    'SUM( color =''', color, ''' ) AS ',color
                    )
       )
  INTO @sql
  FROM sales s;

SET @sql = CONCAT('SELECT WEEK(date) AS WEEK,',@sql,
                   ' FROM sales
                    WHERE YEAR(date) = YEAR(NOW()) 
                    GROUP BY WEEK
                    ORDER BY WEEK');

and then call from php code block such as :

if($stmt = $link->query( @sql )){ ....
$php_data_array = Array(); // create PHP array
....

This way, no need to add each color name to be grouped individually, the query pivots all color column values whichever exist within the table. New colors might be added or existing ones might be deleted without constructing new querie.

Demo (in order to show result of the query)

answered Sep 26, 2020 at 12:53

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

3 Comments

Strawberry Over a year ago

I just don't see what benefit this affords versus handling all of the display logic in the application code

Barbaros Özhan Over a year ago

well, the data might differ, some new color name may be presented as new records inserted. I think no need to individually write each of them in order to pivot. I don't know whether some other pivoting techniques exist in application side, since I don't know php @Strawberry .

Strawberry Over a year ago

Plainly, you know php well enough!! :-)

Collectives™ on Stack Overflow

SQL Query count rows where multiple column-values

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related