Avoid Group by Multiple Columns - Aggregate some columns

Question

Avoid Group by Multiple Columns - Aggregate some columns

Old Hand

Points: 377

October 28, 2019 at 4:43 pm

SELECT 
  PR.PRID,
  PR.PRSName,
  PR.PRLName,
  T.PID,
  T.SID,
  T.MDate,
  T.RType,
  T.PRT,
  T.Sec,
  T.IDate,
  T.OQty,
  T.CQty,
  T.SType,
  T.HID,
  T.HLabel,
  T.Value
FROM TData AS T
INNER JOIN PRData AS PR
ON PR.PID = T.PID
WHERE TR.MDate = @TodaysDate

I want to aggregate these columns now - T.OQty, T.CQty, T.Value

Question: Do I have to include all the columns which are not in the aggregate in GroupBy Clause? Is there any other alternative?

I had started with this. IS there any alternative? OVER Clause???

SELECT PR.PRID
,PR.PRSName
,PR.PRLName
,T.PID
,T.SID
,T.MDate
,T.RType
,T.PRT
,T.Sec
,T.IDate
,T.OQty
,T.CQty
,T.SType
,T.HID
,T.HLabel
,T.Value
FROM (
SELECT PID
,SID
,SType
,MDate
,SUM(OQty) AS OQty
,SUM(CQty) AS CQty
,SUM(Value) AS Value
FROM TData
GROUP BY PID
,SID
,SType
,MDate
) AGG
JOIN TData AS T ON AGG.PID = T.PID AGG.SID = T.SID AGG.SType = T.SType AGG.MDate = T.MDate
JOIN PRData AS PR ON PR.PID = T.PID
WHERE TR.MDate = @TodaysDate

Any help on rewriting SQL Query in efficient way will be helpful.

Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I think SUM(...) OVER(...) is the way to go, with the columns that are to be grouped by in your PARTITION BY clause.

John

drew.allen SSC Guru Points: 76988 More actions · Answer 2

There are many different options here and they all depend on what your final data needs are. Since we don't know what your final data should look like, we can't tell you which of these options would be best.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

The OVER clause can be used on any aggregate function (sum(), avg(), etc.), and fits in well here:

SELECT PR.PRID, PR.PRSName, PR.PRLName, T.PID, T.SID, T.MDate, T.RType, T.PRT, T.Sec, T.IDate, 
       SUM(T.OQty) OVER (PARTITION BY T.PID,T.SID, SType, MDate) AS [OQty],
       SUM(T.CQty) OVER (PARTITION BY T.PID,T.SID, SType, MDate) AS [CQty],
       T.SType, T.HID, T.HLabel, 
       SUM(T.Value) OVER (PARTITION BY T.PID,T.SID, SType, MDate) AS [Value]
FROM TData AS T 
JOIN PRData AS PR ON PR.PID = T.PID
WHERE TR.MDate = @TodaysDate -- To what does the alias ‘TR’ point?

When multiple OVER functions use the same PARTITION BY clause, they will be processed together using the same single spool of the data. The three SUM() OVER (PARTITION BY...) statements above will result in a single aggregation pass through the data.

-Eddie

Eddie Wuerch
MCM: SQL

David Burrows SSC Guru Points: 65142 More actions · Answer 4

I'm with Drew on this one. However some observations

Firstly, the relationship between PRData and TData should be 1-to-many otherwise TData will be repeated many times

Second, GROUP BY will return one row by the grouped column(s) with the values summed within the group

Third SUM(...) OVER(...) will return ALL the rows in TData with the values summed by the column(s) in PARTITION BY

To me GROUPED BY is the correct choice but it depends on the answers to Drew's questions.

Far away is close at hand in the images of elsewhere.
Anon.