CODEWITHSUNDEEP
mysql
Ken Zhang
Ken Zhang

Reputation: 21

How do I generate the table for count of three columns?

I have this table

 |model size color customer|
 |ip4g    8  black    Boy  |
 |ip4g    8  white    Boy  |
 |ip4g    16 white    Girl |
 |ip4g    16 black    Girl |

I know how to query the count by 

Select model, size, color, count(*) from table group by model, size, color;

What I need is to generate an excel table looking like this.

enter image description here

I don't know how I will be able to produce the count which is = 0 and the per customer.

I made a table with all the possible combinations. Then did this query :

select x.model, x.size, x.color, sum(y.customer), count(y.*)
from table x left join table y on x.model = y.model
and x.size = y.size and x.color = y.color group by
x.modelname, x.size, x.color;

I got data fewer than expected. Then, I also need to show all the customers, and the number of customers may vary.

Please help. Thanks.

Upvotes: 0

Views: 48

Answers (1)

Barmar
Barmar

Reputation: 782508

Use SUM() to count the number of rows for each customer:

Select model, size, color, 
        SUM(customer = 'Customer 1') AS Customer1,
        SUM(customer = 'Customer 2') AS Customer2,
        SUM(customer = 'Customer 3') AS Customer3,
        SUM(customer = 'Customer 4') AS Customer4,
        SUM(customer = 'Customer 5') AS Customer5,
        count(*) AS Total
from table 
group by model, size, color;

customer = 'Customer N' is 1 if the customer matches, 0 if it doesn't, so this will count rows for each customer.

Upvotes: 1

Related Questions