Grouping query - missing a trick....

Question

Hi,

I'm missing something which is probably very simple but tearing my hair out all the same, so hope some body can help :-) What i'm trying to achieve is the number of times something has been produced in a day and the average duration per day it takes. Now i'm happy enough with the average duration but the numbers produced i'm really struggling with and i've created a simplified example.

When something is produced it's in a couple of batches but the total produced is for the entire product so i don't want to double count them. So in my example below on the 10th Product1 was produced at 08:00 and 09:00 in 2 batches and the number produced was 10 at 0800 and 50 at 0900 so for product1 on the 10th 60 were produced in total. If I try and sum the number produced i end up with 120 - so i want to group in a way that it sums the number produced based on date and product. Any help welcome!

create table randomtable (
dt datetime,
pname varchar(50),
batch int,
produced int,
duration_secs int
)
insert into randomtable values
('2022-05-10 08:00','product1',1,10,34345),
('2022-05-10 08:00','product1',2,10,43321),
('2022-05-10 09:00','product1',1,50,44467),
('2022-05-10 09:00','product1',2,50,33546),
('2022-05-11 07:00','product1',1,20,33111),
('2022-05-11 07:00','product1',2,20,55222),
('2022-05-11 09:00','product1',1,10,43666),
('2022-05-11 09:00','product1',2,10,33445),
('2022-05-10 08:00','product2',1,25,21554),
('2022-05-10 08:00','product2',2,25,34232),
('2022-05-10 09:00','product2',1,40,11232),
('2022-05-10 09:00','product2',2,40,66888),
('2022-05-11 07:00','product2',1,5,78656),
('2022-05-11 07:00','product2',2,5,22323),
('2022-05-11 09:00','product2',1,100,99456),
('2022-05-11 09:00','product2',2,100,97545)

select datepart(dd,dt) as date,pname,sum(produced)as produced,cast(1.0 * sum(produced * duration_secs) / sum(produced)/1000 as decimal(10,5)) as avg_duration_secs from randomtable
group by datepart(dd,dt),pname
order by 1

Answer 1

Try:

;WITH cte AS (SELECT DISTINCT dt, pName, produced FROM #RandomTable), 
cteSum AS (SELECT DATEPART(DAY,dt) AS [Date], pName, SUM(Produced) AS TotalQty FROM cte GROUP BY DATEPART(DAY,dt), pName)

select datepart(day,r.dt) as date, r.pname, totalQty,
CAST(1.0 * sum(r.produced * duration_secs) / sum(produced)/1000 as decimal(10,5)) as avg_duration_secs 
FROM #randomtable r
INNER JOIN cteSum ON r.pname = cteSum.pName AND DATEPART(DAY,r.dt) = cteSum.[date]
group by datepart(day,r.dt),r. pname, TotalQty
order by [date]

Answer 2

How about sum(produced)/2?

select datepart(dd,dt) as date,pname,sum(produced)/2 as produced,cast(1.0*sum(produced*duration_secs) / sum(produced)/1000 as decimal(10,5)) as avg_duration_secs from randomtable
group by datepart(dd,dt),pname
order by 1

Answer 3

Hi @flakey321
If each product in each time period is the same produced per batch, then you can try to add DISTINCT like this : SUM(DISTINCT produced)AS produced
Check this:

SELECT DATEPART(dd,dt) AS Date,pname,SUM(DISTINCT produced)AS produced  
      ,CAST(1.0*SUM(produced*duration_secs) / SUM(produced)/1000 AS DECIMAL(10,5)) AS avg_duration_secs   
FROM randomtable  
GROUP BY datepart(dd,dt),pname  
ORDER BY 1  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.