Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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
- Store | Product types sold | Discounts given on product types:
- -------------------------------------------------------------
- 1 | 2 | 1
- 2 | 1 | 0
- 3 | 1 | 0
- 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)
- 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)
- 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
Add Comment
Please, Sign In to add comment