How do I count values in different tables using JOIN and/or UNION in MYSQL?-CodePudding

I want to count the product_name in these tables together:

jan_product                feb_product
 ------------               ------------ 
|product_name|             |product_name|
 ------------               ------------ 
|A           |             |A           |
 ------------               ------------ 
|A           |             |B           |
 ------------               ------------ 
|C           |             |C           |
 ------------               ------------

I want my result to look like:

 ------------ --------- --------- 
|product_name|jan_count|feb_count|
 ------------ --------- --------- 
|A           |2        |1        |
 ------------ --------- --------- 
|B           |0        |1        |
 ------------ --------- --------- 
|C           |1        |1        |
 ------------ --------- ---------

So I tried the query below (I'm using MYSQL so I couldnt try FULL JOIN):

SELECT 
    j.product_name, 
    count(j.product_name) as jan_count,
    count(f.product_name) as feb_count
FROM jan_product as j  
JOIN feb_product as f
ON j.product_name = f.product_name
group by j.product_name

UNION

SELECT 
    f.product_name, 
    count(j.product_name) as jan_count,
    count(f.product_name) as feb_count
FROM jan_product as j  
RIGHT OUTER JOIN feb_product as f
ON f.product_name = j.product_name
group by f.product_name
;

But i got this instead:

 ------------ --------- --------- 
|product_name|jan_count|feb_count|
 ------------ --------- --------- 
|A           |2        |2        | --- A counts for FEB is wrong
 ------------ --------- --------- 
|B           |0        |1        |
 ------------ --------- --------- 
|C           |1        |1        |
 ------------ --------- ---------

I do not know what to do to get to the expected result.

CodePudding user response：

We can do this with count from Union all. I would rather advise you to have one table with a month column.

create table jan (pname char(1));
create table feb (pname char(1));
insert into jan values('A'),('A'),('C');
insert into feb values ('A'),('B'),('C');
select
  pname, 
  count(j) jan,
  count(f) feb
from
  (select pname,pname j,null f from jan
    union all
  select pname,null,pname from feb) jf
group by pname

pname | jan | feb
:---- | --: | --:
A     |   2 |   1
B     |   0 |   1
C     |   1 |   1

db<>fiddle here

CodePudding user response：

Ignoring the table structure for a minute ... try using a UNION ALL, including an extra column to indicate the source month. Then use a conditional SUM to calculate the totals for each month.

See also db<>fiddle

SELECT  t.product_name
        , SUM( CASE WHEN t.month_number = 1 THEN 1 ELSE 0 END) AS jan_count
        , SUM( CASE WHEN t.month_number = 2 THEN 1 ELSE 0 END) AS feb_count
FROM   (
           SELECT CAST(1 AS UNSIGNED) AS month_number, product_name
           FROM   jan_product
           UNION ALL 
           SELECT CAST(2 AS UNSIGNED) AS month_number, product_name
           FROM   feb_product
       ) t      
GROUP BY t.product_name

Results:

product_name | jan_count | feb_count
:----------- | --------: | --------:
A            |         2 |         1
C            |         1 |         1
B            |         0 |         1

Having said that, you should normalize the model. You could greatly simplify things by storing everything in a single table, with a date (or month year columns) instead of having a separate table for each month.

Also, it seems like you're storing information about events that occur to a specific product over time. If that's the case, you should have a separate table containing unique products:

ProductId	ProductName
1	Product A
2	Product B
3	Product C

Other tables that store information about products should store the "Product" table's unique PK (primary key) value - not a product's name. For example, if you had a ProductSales table

 | ProductId | SaleDate    | Quantity |
 |-----------|-------------|----------|
 |   1       | 02/01/2022  | 15       |
 |   2       | 02/10/2022  | 4        |
 |   1       | 02/12/2022  | 3        |
 |   3       | 02/01/2022  | 20       |

To retrieve information about the sales by month, all you'd need is a simple JOIN between the two tables

See also db<>fiddle

SELECT p.product_name
       , year(s.sales_date) AS sales_year
       , SUM( CASE month(s.sales_date) WHEN 1 THEN 1 ELSE 0 END) AS jan_sales
       , SUM( CASE month(s.sales_date) WHEN 2 THEN 1 ELSE 0 END) AS feb_sales
       , SUM( CASE month(s.sales_date) WHEN 3 THEN 1 ELSE 0 END) AS mar_sales
       -- ... etc
FROM   product p LEFT JOIN product_sales s ON s.product_id = p.product_id
GROUP BY p.product_name
        , year(s.sales_date) 
;

Results:

product_name | sales_year | jan_sales | feb_sales | mar_sales
:----------- | ---------: | --------: | --------: | --------:
Product A    |       2022 |         0 |         2 |         0
Product B    |       2022 |         0 |         1 |         0
Product C    |       2022 |         0 |         1 |         0

CodePudding user response：

--Please try using below query
-------
WITH cte AS
           ( SELECT * FROM jan_product
             UNION
             SELECT * FROM feb_product)

SELECT cte.product_name,
       j.Jan_count,
       count(f.product_name) as February_count
FROM cte
LEFT JOIN (SELECT product_name,
           COUNT(product_name) as Jan_count
           FROM jan_product
           GROUP BY product_name) j
ON cte.product_name=j.product_name
LEFT JOIN (SELECT product_name,
           COUNT(product_name) as Feb_count
           FROM feb_product
           GROUP BY product_name) f
ON cte.product_name=f.product_name