MySQL dynamic crosstab over two columns

Ladies and Gentlemen,

I have the following problem in MySQL 5.1. Here's my table.

Sale | Store | Product | Discount
---------------------------------
 1   |   1   |   B     | Yes
 2   |   1   |   B     | Yes
 3   |   1   |   B     | No
 4   |   1   |   D     | Yes
 5   |   1   |   A     | No
 6   |   2   |   A     | No
 7   |   3   |   B     | No
 8   |   3   |   B     | No
 9   |   1   |   D     | Yes
10   |   2   |   A     | No

Now this is of course a terribly inconvenient way to organize data, but that's how the data happened to come in (and will continue to come in).

I need to be able to throw at it an entirely arbitrary list of product types开发者_C百科 (comma-separated if you will); let's say A,B,C. In the end, I need a list of what store sold what. Here's an intermediate result to make it easier to understand.

Store | Product | Sales | Discounts
-----------------------------------
  1   |   A     |  1    | 0
  1   |   B     |  3    | 2
  1   |   C     |  0    | 0
  2   |   A     |  2    | 0
  2   |   B     |  0    | 0
  2   |   C     |  0    | 0
  3   |   A     |  0    | 0
  3   |   B     |  2    | 0
  3   |   C     |  0    | 0

What I need, in the end, is this:

Store | Product types sold | Discounts given on product types:
-------------------------------------------------------------
  1   |         2          |        1
  2   |         1          |        0
  3   |         1          |        0

The third column says how many of the sold (and queried) product types were given at least one discount.

I've tried with all kinds of dynamic crosstab-generating queries (yes, I've seen them all), but I don't have enough mental RAM to wrap my head around everything in one go. How would you approach this the most efficient way? Temporary tables/stored procedures etc. are okay, rough outlines of ideas are very welcome. Thank you!

You can group your data by store and product to produce your intermediate result:

mysql> SELECT Store, Product,
COUNT(1) AS Sales,
SUM(IF(Discount='Yes', 1, 0)) AS Discounts
FROM input
WHERE Product IN ('A', 'B', 'C', 'D')
GROUP BY Store, Product
+-------+---------+-------+-----------+
| Store | Product | Sales | Discounts |
+-------+---------+-------+-----------+
|     1 | A       |     1 |         0 |
|     1 | B       |     3 |         2 |
|     1 | D       |     2 |         2 |
|     2 | A       |     2 |         0 |
|     3 | B       |     2 |         0 |
+-------+---------+-------+-----------+
5 rows in set (0.00 sec)

Then you can group this result just by store to convert it to your final result:

mysql> SELECT Store,
COUNT(1) AS `Product types sold`,
SUM(Discounts) AS `Discounts given on product types`
FROM (
SELECT Store, Product,
COUNT(1) AS Sales,
SUM(IF(Discount='Yes', 1, 0)) AS Discounts
FROM input
WHERE Product IN ('A', 'B', 'C', 'D')
GROUP BY Store, Product
) AS intermediate
GROUP BY Store
+-------+--------------------+----------------------------------+
| Store | Product types sold | Discounts given on product types |
+-------+--------------------+----------------------------------+
|     1 |                  3 |                                4 |
|     2 |                  1 |                                0 |
|     3 |                  1 |                                0 |
+-------+--------------------+----------------------------------+
3 rows in set (0.00 sec)

Note that the first query is a sub-query of the second.

I think is just separating queries is easiest:

select Store, 
   (Select count( distinct Product) from products as p2 where p2.Store = p1.Store),
   (Select count(distinct Product) from products as p3 where p3.Store = p1.Store and p3.Discount = "Yes")
from products p1
group by Store

(If it is about large tables, be sure to test your performance.)