Question 21402 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL COUNT with conditions – Mysql

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.

product_id start_month end_month
1 3 12
2 1 2
3 4 6
4 4 8
5 5 5
6 10 11

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>

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search