SQL - Trying to count rows based on a date range, some rows have the same date; when rows have the s-CodePudding

I'm trying to figure out this query.. here is what my data set looks like:

I need to count how many times an employee created more than 7,000 widgets on a given day for the month of January, regardless of color. So when the completion date is the same it will SUM the Total_widgets_created then have the SUM considered for the count result.

Expected result:

Willy wonka's total output on 01/22 is 10,200 thus exceeding the threshold of 7,000 so I want that to be counted once. If Willy Wonka had another line with the date of 01/16/2022 for 8,500 then it would be a count of 2.

CodePudding user response：

select employee_id, first_name, last_name, month(completion_date),
sum(case when total_widgets_created > 7000 then 1 else 0 end) as total_cnt
from table
group by employee_id, first_name, last_name, month(completion_date)

The above will produce data in row format based on months. You can write a nested query to transpose this to columns using group_concat or case statements.

CodePudding user response：

You can use a conditional aggregation through grouping by employee_id, first_name, last_name, TRUNC(completion_date) while restricting results to the month January of the current day, presuming your DB is Oracle considering the previous posts, such that

SELECT employee_id, first_name, last_name, 
       SUM(CASE WHEN TRUNC(completion_date) > 7000 THEN 1 ELSE 0 END) "7K WIDGETS IN JANUARY"
  FROM t
 WHERE TO_CHAR(completion_date,'yyyymm') = TO_CHAR(sysdate,'yyyy')||'01' 
 GROUP BY employee_id, first_name, last_name, TRUNC(completion_date)

eg. the important trick to consider is keeping the truncated date value within the group by list.