I have this table that lists which months each product is available on the market. For example product 1 is available from Mar to Dec and product 2 is available from Jan to Feb.

I need to count how many product_ids each month of the year has but can't think of how to put: WHERE month >= start_month AND month >= end_month. Can I use a loop for this or would that be overkill>

CodePudding user response：

I used dbFiddle to test out this solution. It's dependent on there being at least 1 product available for sale in each month. Although, maybe it's better that a month isn't returned when there isn't a product for sale?

Could use @derviş-kayımbaşıoğlu approach to generating the months, but not group on product_id, but on month instead.

with months as (
    Select distinct start_month [month]
    from Product
)

Select m.month
    ,count(*) [products]
from months m
  left join Product p
  on m.month >= p.start_month and m.month <= p.end_month
group by m.month

CodePudding user response：

something like this needs to help but you may have syntax error since we don't know exact DBMS and version

select product_id, count(*) cnts
from table1 
inner join (
  select 1 month union
  select 2 union
  select 3 union
  select 4 union
  select 5 union
  select 6 union
  select 7 union
  select 8 union
  select 9 union
  select 10 union
  select 11 union
  select 12 union
) t2
on t2.month between table1.start_month and table1.end_month
group by product_id