question

flakey321 avatar image
0 Votes"
flakey321 asked flakey321 commented

Grouping query - missing a trick....

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is the output you expected based on your sample data?

0 Votes 0 ·
flakey321 avatar image flakey321 GuoxiongYuan-7218 ·

Hi thanks for the quick response

So i'm wanting to get the number of each product produced each day and the average time it took to produce. The average duration i'm happy with it's the numbers produced i'm struggling with so what i want to see is

10th Product1 60 produced
10th Product2 65 produced
11th Product1 30 produced
11th Product2 105 produced

Thank you

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered flakey321 commented

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]
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ah yes perfect thank you so much! I'd had a go with a temp table and not quite got there but that delivers exactly what I need. I really appreciate you taking the time out to help me and such a quick response!

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered flakey321 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks, the problem i'd hit in the real world issue i'm looking at would be that the number of batches would always change so i couldn't hard wire in the /2 but thanks for having a look. The answer from Naomi does the trick

0 Votes 0 ·
LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered flakey321 commented

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:
201198-image.png

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.


image.png (3.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi thanks for the reply. That was one of the solutions i'd tried which looked ok initially but then the problem is if we have another timeslot say on the 10th where another batch of 10 were produced for product1 it wouldn't add these on as we've already pulled out the distinct value of 10

0 Votes 0 ·