MySQL - Group results depending on binary criteria-CodePudding

i'm looking for a way to get my result in a certain way out of my sql query.

Sorry for the bad title, if you have any suggestion to explain my problem better, tell me, and i'll change it. It's a bit difficult to explain with simples words so let me show you what i've and what i would like to get :

Here is the database i'm working on It's filled up by a script, seeking on a printer server for any printing query

DB structure

My query is here :

SELECT SUM(pages*copies) as "nbpages", user, Grayscale
            FROM presentation
            WHERE Grayscale NOT LIKE ""
            GROUP BY user, Grayscale
            ORDER BY `user`  ASC, grayscale ASC;

The WHERE clause is only here to have two options : "GRAYSCALE" Or "NOT GRAYSCALE"

Here are the result

nbpages	user	grayscale
654	user1	GRAYSCALE
5689	user1	NOT GRAYSCALE
856	user2	GRAYSCALE
45	user3	GRAYSCALE
30	user3	NOT GRAYSCALE

And i'll like to get something like :

user	nbgray	nbnogray
user1	654	5689
user2	856	0
user3	45	30

Any suggestions ?

CodePudding user response：

Looks like a simple condition aggregation to me:

select
user,
sum (case when grayscale = 'GRAYSCALE' then pages*copies else 0 end) as nbgray,
sum(case when grayscale <> 'GRAYSCALE' then pages*copieselse 0 end) as nbnogray
from
<table>
group by
user

CodePudding user response：

Use conditional aggregation:

SELECT user,
       SUM(CASE WHEN Grayscale = 'GRAYSCALE'
                THEN pages*copies ELSE 0 END) AS nbgray,
       SUM(CASE WHEN Grayscale = 'NOT GRAYSCALE'
                THEN pages*copies ELSE 0 END) AS nbnogray
FROM presentation
GROUP BY user
ORDER BY user;