Get selected products summery from another table-CodePudding

I have a table1 where column names are there

line	product
1	a
2	b
3	d

Another table2 product sales

a	b	c	d
l	s	m	xs
xs	s	xs	m
m	xl	s	l
xs	xl	m	xl
xs	xl	l	s
xs	m	xl	l
m	m	l	l
s	m	m	l

And I want a summery of products from table2 based on selected products in table1. Ay suggestion on this please?

The expected result looks like;

product	sizes	qty
a	l	1
a	xs	4
a	m	2
a	s	1
b	s	2
b	xl	3
b	m	3
d	xs	1
d	m	1
d	l	4
d	xl	1
d	s	1

CodePudding user response：

You need to first linearize your sales table, by changing the schema from <a,b,c,d> to <product, sizes> using the UNION ALL as shown in the cte below.

Then you can:

use INNER JOIN to filter your needed products,
use COUNT to generate your qty field, by aggregating on both product and sizes

WITH cte_sales AS (
    SELECT 'a' AS product, a AS sizes FROM sales 
    UNION ALL
    SELECT 'b' AS product, b AS sizes FROM sales
    UNION ALL
    SELECT 'c' AS product, c AS sizes FROM sales
    UNION ALL
    SELECT 'd' AS product, d AS sizes FROM sales
)
SELECT s.product, s.sizes, COUNT(s.sizes) AS qty
FROM       products  p
INNER JOIN cte_sales s
        ON p.product = s.product
GROUP BY s.product, s.sizes
ORDER BY s.product, s.sizes

If you know in advance you want only a, b and c sales, and want to avoid the join, you can also do as follows:

WITH cte_sales AS (
    SELECT 'a' AS product, a AS sizes FROM sales 
    UNION ALL
    SELECT 'b' AS product, b AS sizes FROM sales
    UNION ALL
    SELECT 'd' AS product, d AS sizes FROM sales
)
SELECT s.product, s.sizes, COUNT(s.sizes) AS qty
FROM cte_sales s
GROUP BY s.product, s.sizes
ORDER BY s.product, s.sizes

Output:

product	sizes	qty
a	l	1
a	m	2
a	s	1
a	xs	4
b	m	3
b	s	2
b	xl	3
d	l	4
d	m	1
d	s	1
d	xl	1
d	xs	1

Check the demo here.