-2

Despite research i maked to find the answer in Stack on advised article i cannot find a way to make this query.

I want to know how many distinct ids share the same group of value (brand, category, start_date and end_date)

table1:

id brand category start_date end_date
Xy-eee2 mercury alpha 05/12/2021 16/12/2021
Xd-FF456 mercury alpha 05/12/2021 16/12/2021
Xy-234 mercury alpha 05/12/2021 16/12/2021
Xd-345 mercury alpha 05/12/2021 16/12/2021
Xy-eee2 mercury alpha 05/12/2021 16/12/2021
Xd-FFF21 venus beta 20/03/2021 25/04/2021
Xy-eee2 venus beta 20/03/2021 25/04/2021
Xd-FF23 venus beta 20/03/2021 25/04/2021
Xd-FF45 mars beta 01/03/2022 06/08/2021

Expected result :

brand category start_date end_date number_of_distinct_id_matching
mercury alpha 05/12/2021 16/12/2021 5
venus beta 20/03/2021 25/04/2021 3
mars beta 01/03/2022 06/08/2021 1
Gabriel
  • 35
  • 5

1 Answers1

0

Is that what you need?

Select brand, category, min(start_date) start_date, max(end_date) end_date, count(1) [Count]
FROM table_name
group by  brand, category
Meyssam Toluie
  • 1,061
  • 7
  • 21
  • Thanks for your answer! I'm looking for the count of distinct id who match with every duplicate of brand, category, start_date and end_date. Your query doesn't include the id – Gabriel Sep 14 '22 at 12:02