Home > front end >  MySQL - Group results depending on binary criteria
MySQL - Group results depending on binary criteria

Time:04-13

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

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;
  • Related