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;