SQL Server: SUM by grouped column and sort by another column

Question

I'd like to combine all like Locations (name) and SUM the Volume (vol) for that location and order the results by Day of Month here. I attempted to Select a subset and inner join the values like here but kept getting group by not in aggregate clause errors, thank for the hints!! Results should be ordered by day of month and lines with the same location on same day should be summed on one line instead of multiple lines. Here is my Select Statement without grouping or summing:

SELECT       
    day_of_month AS 'Day of Month',
    run_ticket   AS 'Ticket number',
    ticketdate   AS 'Ticket Date',
    id           AS 'location id',
    name         AS 'location',
    vol          AS 'Volume',
    ord_rev      AS 'OrderHeader'
FROM 
    #RECORDS R
ORDER BY 
    day_of_month

Current Results: Ordered by day of month, same locations for that day are not summed on one line.

Desired Result: Ordered by day of month, same locations for that day are summed on one line. I am also summing total vol per day and for the dates run but doing that in SSRS.

I was trying this solution as such

SELECT       day_of_month       AS 'Day of Month'        
            ,run_ticket         AS 'Ticket Number' 
            ,ticketdate         AS 'Ticket Date'
            ,r2.cmp_id          AS 'Location ID' 
            ,cmp_name           AS 'location'
            ,SUM(vol)           AS 'Volume'
            ,ord_rev            AS 'OrderHeader'


FROM #RECORDS as r2

 JOIN 
 (SELECT cmp_id, SUM(vol) AS 'Volume'
      FROM #RECORDS
      GROUP BY cmp_name
   ) AS s ON s.cmp_id = r2.cmp_id

GROUP BY  r2.cmp_name

ORDER BY day_of_month

When I run the proc I get

Msg 8120, Level 16, State 1, Procedure DailyLoadReportMTD, Line 78 [Batch Start Line 109] Column '#RECORDS.cmp_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ok, Erics answer got me this close! just need to eliminate dupes If I GROUP BY r.cmp_name, r.ord_revtype2, r.day_of_month I had to add r.ofr_BSWheight which forces a duplicate line... or throws the invalid select error w/o it

Please show us sample data and expected results (as tabular text) to clarify your question. — GMB, Nov 13 '19 at 22:30
I don't see in the code you posted, that will cause the error you mentioned. — Eric, Nov 13 '19 at 22:46
All those non-aggregated columns are not in the `GROUP BY`. Thus the error. — Eric, Nov 14 '19 at 17:55
In the outer query, you are grouping the records by `cmp_name`. So what happen if you have multiple records with the same `cmp_name`, but different `run_ticket` or `ord_rev`. Which one will be picked in this case? — Eric, Nov 14 '19 at 17:57
So you either have to put those columns in the `GROUP BY`, or you aggregate those columns. — Eric, Nov 14 '19 at 17:59
@Eric ok I am getting there now.. So run_ticket is actually useless, we can ignore, ord_rev may be a multi select on my next iteration of the report, but is currently only taking one argument. — Ray Koren, Nov 14 '19 at 18:02

score 0 · Answer 1 · answered Nov 13 '19 at 23:21

0

The problem description begs the question "which day_of_month do you want to order by".

Since you are grouping on name to get a sum of vol, it is fair to assume that there will be more than one R per name, each of which could have different day_of_month values.

Possible valid ordering expressions that would not result in "not in aggregate" errors are ORDER BY MIN(day_of_month) or ORDER BY MAX(day_of_month).... you could even use AVG or SUM, but those wouldn't make much sense.

Also, ' is a string delimiter, not an identifier delimiter. In MSSQL you use the ansi standard " or the MS-specific [ and ].

answered Nov 13 '19 at 23:21

Uueerdo

15,723
1
16
21

Results should be ordered by day of month and same location on same day should be summed on one line instead of multiple lines. – Ray Koren Nov 14 '19 at 17:50
1

@RayKoren It sounds like you should be grouping by location and day_of_month. – Uueerdo Nov 14 '19 at 18:56
its not that simple sorry – Ray Koren Nov 14 '19 at 19:05

Eric · Answer 2 · 2019-11-14T20:59:33.880

0

Since you want all those columns, probably window function will work better. Will this work?

SELECT       day_of_month       AS 'Day of Month'        
            , run_ticket         AS 'Ticket Number' 
            , ticketdate         AS 'Ticket Date'
            , cmp_id          AS 'Location ID' 
            , cmp_name           AS 'location'
            , SUM(vol) OVER(PARTITION BY day_of_month, cmp_name)           AS 'Volume'
            , ord_rev            AS 'OrderHeader'
FROM #RECORDS

Since you remove a few columns, simple GROUP BY should work.

SELECT day_of_month       AS 'Day of Month'        
    , cmp_id          AS 'Location ID' 
    , cmp_name           AS 'location'
    , SUM(vol)           AS 'Volume'
    , ord_rev            AS 'OrderHeader'
FROM #RECORDS
GROUP BY day_of_month, cmp_id, cmp_name, ord_rev

edited Nov 14 '19 at 20:59

answered Nov 14 '19 at 18:06

Eric

3,165
1
19
25

Sorry that is returning total sums for the location for the whole date range. like still multiple lines per lease just with the total sum for the whole run on each line – Ray Koren Nov 14 '19 at 18:20
1

I added `day_of_month` in the partition. – Eric Nov 14 '19 at 18:43
Much closer, Volumes are correct, just have to remove the duplicate rows somehow! – Ray Koren Nov 14 '19 at 19:01
If there are multiple values of `run_ticket`, `ticketdate`, `ord_rev`, which one would you pick? – Eric Nov 14 '19 at 19:12
Remove run ticket and ticket date and sub group by ord_rev see updated question! Thanks! – Ray Koren Nov 14 '19 at 20:53

score 0 · Answer 3 · answered Nov 15 '19 at 19:48

I figured it out using another temp table and CTE

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#RECORDS') IS NOT NULL
BEGIN
    DROP TABLE #RECORDS
END
IF OBJECT_ID('tempdb..#RECORDS2') IS NOT NULL
BEGIN
    DROP TABLE #RECORDS2
END
-- Insert statements for procedure here
CREATE TABLE #RECORDS
(
             day_of_month    int
            --,run_ticket        varchar(255)
            ,inv_seal_ondate datetime   
            ,cmp_id          varchar(255)
            ,cmp_name        varchar(255)   
            ,ofr_BSWHeight   decimal
            ,ord_revtype2    varchar(255)

)
    CREATE TABLE #RECORDS2
(
             day_of_month    int
            --,run_ticket        varchar(255)
            ,inv_seal_ondate datetime   
            ,cmp_id          varchar(255)
            ,cmp_name        varchar(255)   
            ,ofr_BSWHeight   decimal
            ,ord_revtype2    varchar(255)

)

-- Initial population of Records temp table ------------------

INSERT INTO #RECORDS
(            day_of_month
            --,run_ticket        
            ,inv_seal_ondate 
            ,cmp_id          
            ,cmp_name           
            ,ofr_BSWHeight
            ,ord_revtype2   

)                                           

    SELECT  
             (SELECT DAY(inv_seal_ondate)) --day_of_month extracted from inv_seal_ondate
            --,o.run_ticket     
            ,o.inv_seal_ondate   
            ,c.cmp_id           
            ,c.cmp_name         
            ,o.ofr_BSWHeight
            ,oh.ord_revtype2    
    FROM OFR o
    INNER JOIN company c on o.cmp_id = c.cmp_id 
    INNER JOIN orderhead oh on oh.ord_hdrnumber = o.ord_hdrnumber

    WHERE   o.inv_seal_ondate between @StartDate and @EndDate
    AND     c.cmp_altid in (@Company_AltId)

    AND     oh.ord_revtype2 in (@RevType2)

INSERT INTO #RECORDS2
(            day_of_month
            --,run_ticket        
            ,inv_seal_ondate 
            ,cmp_id          
            ,cmp_name           
            ,ofr_BSWHeight
            ,ord_revtype2   

)   
SELECT       
             day_of_month       AS 'Day of Month'
            --,run_ticket       AS 'Run Ticket' 
            ,inv_seal_ondate    AS 'Ticket Date'
            ,cmp_id         AS 'Lease ID' 
            ,cmp_name           AS 'Lease Name'
            ,SUM(ofr_BSWHeight) OVER(PARTITION BY day_of_month  , cmp_name)       AS 'NSV'
            ,ord_revtype2       AS 'OrderHeaderRevType2'


FROM #RECORDS r
ORDER BY day_of_month

;WITH cte AS (
    Select 
                day_of_month        

                ,inv_seal_ondate    

                ,cmp_name           
                ,ofr_BSWHeight      
                ,ord_revtype2       
                ,ROW_NUMBER() OVER (
                    PARTITION BY 
                         day_of_month 
                        ,cmp_name           

            ORDER BY 
                day_of_month
        ) row_num

     FROM 
        #RECORDS2
)
DELETE FROM cte
WHERE row_num > 1;
-------------- Return Primary Result Set ----------------------------

SELECT       
             day_of_month       AS 'Day of Month'
            --,run_ticket           AS 'Run Ticket' 
            ,inv_seal_ondate    AS 'Ticket Date'
            ,cmp_id             AS 'Lease ID' 
            ,cmp_name           AS 'Lease Name'
            ,ofr_BSWHeight      AS 'NSV'
            ,ord_revtype2       AS 'OrderHeaderRevType2'


    FROM #RECORDS2 r2
    ORDER BY day_of_month



END

SQL Server: SUM by grouped column and sort by another column

3 Answers3