Count item after joining two table-CodePudding

I have two tables, First is Product table

  ----------- ------ ----- ------------ 
 | id  | pnum | year |month|date        |
  ----------- ------ ----- ------------ 
 |12   |  S5 | 2021 | 2   | 2021-02-21 |
 |12   |  S5 | 2021 | 2   | 2021-02-22 |
 |12   |  S5 | 2021 | 2   | 2021-02-23 |
 |33   |  A55| 2021 | 3   | 2021-03-01 |
 |44   |  B1 | 2021 | 6   | 2021-06-01 |

Second is Deal table

  ----------- ------ ----- ------------ 
 | id  | pnum| year |month|date        |
  ----------- ------ ----- ------------ 
 |12   |  S5 | 2021 | 2   | 2021-02-28 |
 |12   |  S5 | 2021 | 2   | 2021-02-01 |
 |33   |  A55| 2021 | 3   | 2021-03-01 |

I need a result which can tell me how many product got launch for a year_month and count of deal in first 15 days or in next 15 days

  -----------  ------------ ---------------- -------------------- 
 | num | count| year-month |deal_in_first15 |deal_after_first15 |
  -----------  ------ ----- -------------------------------------
 |S5   |  3   | 2021-02    |      1         |       1           |
 |A55  |  1   | 2021-03    |      1         |       0           |

I was trying to do it like below

 select * from Product p inner join Deal d on
 p.num=d.num AND p.id=d.id AND p.month=d.month

but it is not helping me to get exact result as intended.

I have some java and python background and not expert in sql hence applying count and case statement is not working out.

CodePudding user response：

You can try to use the condition aggregate function in subquery then do JOIN

select p.pnum ,
       COUNT(*) count,
       FORMAT(p.[date],'yyyy-MM') 'year-month',
       deal_in_first15 ,
       deal_after_first15
from Product p 
inner join (
    SELECT id ,
        pnum ,
        month,
        year ,
        COUNT(CASE WHEN DATEPART(day,[date]) < 15 THEN 1 END) deal_in_first15 ,
        COUNT(CASE WHEN DATEPART(day,[date]) >= 15 THEN 1 END) deal_after_first15 
    FROM Deal 
    GROUP BY id ,pnum ,month,year
) d on
 p.pnum=d.pnum AND p.id=d.id AND p.month=d.month
 group by  FORMAT(p.[date],'yyyy-MM') ,
          p.pnum,
          deal_in_first15 ,
          deal_after_first15

I think there is another way might you want, using two subquery then JOIN

select p.pnum ,
       p.cnt 'count',
       CONCAT(p.year,'-',FORMAT(p.month,'0#')) 'year-month',
       deal_in_first15 ,
       deal_after_first15
from (
    SELECT id ,
        pnum ,
        month,
        year,
        count(*) cnt
    FROM Product
    GROUP BY  id ,
        pnum ,
        month,
        year
) p 
inner join (
    SELECT id ,
        pnum ,
        month,
        year ,
        COUNT(CASE WHEN DATEPART(day,[date]) < 15 THEN 1 END) deal_in_first15 ,
        COUNT(CASE WHEN DATEPART(day,[date]) >= 15 THEN 1 END) deal_after_first15 
    FROM Deal 
    GROUP BY id ,pnum ,month,year
) d on
 p.pnum=d.pnum 
 AND p.id=d.id 
 AND p.month=d.month
 AND p.year=d.year

sqlfiddle

Note

I would use the year column to be JOIN condition. otherwise, the result will be wrong when you cross-year meet the same month.

CodePudding user response：

This is it:

select pnum,cnt,sum(mn1),sum(mn2) from  (select  d."pnum",(select count(*) from Product p where p."pnum"=d."pnum"
                ) cnt,
case when
EXTRACT(DAY FROM d."date")<=15 then sum(1) else sum(0) end mn1 , 
case when
EXTRACT(DAY FROM d."date")>15 then sum(1) else sum(0) end mn2,
d."year"||'-'||d."month"
from deal d  
group by 1,d."year"||'-'||d."month",d."date")abc group by 1,2;

please check at:http://sqlfiddle.com/#!17/3bad9/18