dealer  desktop tablet
A       1       0
A       1       0
B       0       1
C       0       0
A       0       1
A       1       0
C       0       1
C       0       0
C       0       0
B       0       1
B       0       0
B       0       0
A       0       0

Not very good at SQL

What I want to do here is

1. Group by dealer
2. Get the count of desktop, laptop(when desktop = 0 and tablet = 0) and tablet

SELECT dealer, COUNT(*) FROM table
GROUP BY dealer

got me the count of dealers, but based on the criteria, I don't know how to get it.

What I expect is a row of counts.

dealer | laptop | desktop | tablet

CodePudding user response：

Maybe something like this:

    select dealer, 
       COALESCE(SUM(case when desktop = 0 and tablet = 0 then 1 end),0) as laptop,
       COALESCE(SUM(case when desktop = 1 then 1 end),0) as desktop,
       COALESCE(SUM(case when tablet = 1 then 1 end),0) as tablet
from test
group by dealer;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=47d3d7761784bda3b94413f6603e06f7

CodePudding user response：

select dealer, sum(desktop) as desktop, sum(tablet) as tablet, sum(

First you have to create another column for laptop. i would just use a case when statement, google it for further details.

( select *, case (when desktop tablet = '0' then '1' else '0' end) as laptop from ____ )

Then, you can call back different sums and group by dealer!