0

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.

Ken Zhang
  • 21
  • 7

1 Answers1

1

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks @Barmar . How do I get the total count if not all customers will be included? Also, what if the number of customers is not the same at all times? thank you in advance. – Ken Zhang Feb 06 '15 at 02:51
  • You need a table that lists all the possible model, size, and color combinations. You can then use a `LEFT JOIN` with this query to generate zero values for the combinations that have no customers. – Barmar Feb 06 '15 at 02:58
  • Hi @barmar, I already made the table for the list of possible combinations, and left joined it. How will I query this if the number of customers is dynamic? Sometimes, I will only have 2 customers, and sometimes, 3. – Ken Zhang Feb 06 '15 at 08:27
  • See http://stackoverflow.com/questions/7674786/mysql-pivot-table for how to do a dynamic pivot in MySQL. – Barmar Feb 06 '15 at 17:06