BASIC SQL - Struggling with Group By Statements and Summing

Question

I am writing the following SQL script, and am encountering the error listed directly below said script:

  SELECT

       concat(month([sbi].[dtmDelivered]),'-',year([sbi].[dtmDelivered])) as rdate
       ,o.[strCompanyNodeName]
       ,sum(sbi.[mnyDollarcost])
       ,p.[strProductName]
       ,p.[strProductType]
       ,o.[strSalesRegionNodeGroup]
       ,o.[strSalesRegionNodeName]
       ,concat(o.[strMasterSalesFirstNameNode],' ',o.[strMasterSalesLastNameNode]) AS "Sales_Rep"  FROM [sqlSalesBI].[Fact].[uvwReport] as "sbi"

  LEFT JOIN [sqlDim].[dbo].[tbldimProduct] as "p" ON [sbi].[intDimProductPrimaryID] = p.intDimProductID
  LEFT JOIN [sqlSalesBI].[Dim].[uvwOrgNode] as "o" ON [sbi].[intOrgNodeID] = o.intOrgNodeID
  LEFT JOIN [sqlDim].[dbo].[tbldimStatus] as "t" ON [sbi].[intDimStatusID] = t.intDimStatusID

  WHERE sbi.intDimStatusID = 5 and sbi.mnyDollarcost >0 and sbi.dtmDelivered >= '2015-01-01 00:00:00.000'

  GROUP BY o.strCompanyNodeName

ERROR I RECEIVE WHEN EXECUTING

Msg 8120, Level 16, State 1, Line 3
Column 'sqlSalesBI.Fact.uvwReport.dtmDelivered' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'sqlSalesBI.Fact.uvwReport.dtmDelivered' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What I am ultimately hoping to accomplish is an end table, where the revenue ('mnydollarcost') is summed and grouped by month and year ('rdate'), as well as by company ('strCompanyNodeName').

Here's a table demonstrating what I'd like the outcome to be

rdate           strcompanynodename          Sum of MnyDollar Cost         other columns >>>
    ---------------------------------------------------------------------------------- 
7-2017          Chadwick Supply Co.                5100
7-2017          Northeastern Milling               5600
7-2017          Ford Paper                         25320
7-2017          Cleveland Paper Co.                1020
8-2017          Chadwick Supply Co.                1200
8-2017          Northeastern Milling               5600
8-2017          Ford Paper                         58450
8-2017          Cleveland Paper Co.                1200

I am extremely new to SQL, so thank you for your patience in advance!

It's really simple actually. Unless you're connecting to a MySql, when a GROUP BY is used, then the fields you select that are not used in an aggregate function (SUM, MAX, COUNT, ...) also have to be listed in the GROUP BY. — LukStorms, Jul 25 '18 at 12:47
If you are not aggregating a field with an aggregate formula (`sum()`, `max()`, etc) in the `SELECT` clause then it MUST be present in the `GROUP BY`. Add those columns to your `GROUP BY` clause and resubmit. — JNevill, Jul 25 '18 at 12:54
Okay, so what I've done in response to your first comment is add to my group by statement. It now reads: GROUP BY o.[strCompanyNodeName], p.[strProductName], p.[strProductName], p.[strProductType], o.[strSalesRegionNodeGroup], o.[strSalesRegionNodeName], 'sales_rep', 'rdate' But still receiving error: Msg 164, Level 15, State 1, Line 16 Each GROUP BY expression must contain at least one column that is not an outer reference. @LukStorms — James, Jul 25 '18 at 12:56
@LukStorms I caught that about 2 seconds after submitting and removed my comment. — JNevill, Jul 25 '18 at 12:56
@James Instead of using alias names like `rdate`, use the real name in the group by. f.e. `sbi.dtmDelivered`. — LukStorms, Jul 25 '18 at 13:00
@LukStorms Holy crap, that worked, as my query is executing without issue. So is the lesson with the group by is to never use aliases for group by statements? Seems like that could get messy/be hard to understand. — James, Jul 25 '18 at 13:03
Btw, you don't have to quote every name with `[]` or `"`. If the name doesn't contain spaces and isn't some keyword then leaving them unquoted is fine. Although that's sometimes also a matter of taste. — LukStorms, Jul 25 '18 at 13:03
@James, I actually agree. And I think that in some relational databases it's allowed to use the alias names in the group by. Not in MS SQL. If you do have the need to group on an alias name, then you could put the query in a sub-query. And then group on that alias name in the outer query. — LukStorms, Jul 25 '18 at 13:07
Hmm okay. Noted. So the query finished and it looks great! BUT the header for that rdate column reads "no column name" which is a bit of an aesthetic bummer. How would I rename without an alias? — James, Jul 25 '18 at 13:15
@James You can give it an alias name. Just don't use that alias name in the group by. f.e. `select bar * 10 as bar10, count(*) from foo group by bar` is fine. But `select bar * 10 as bar10, count(*) from foo group by bar10` fails. — LukStorms, Jul 25 '18 at 13:20

score 0 · Answer 1 · answered Jul 25 '18 at 12:58

0

Looks like your query is are not getting aggregated. Add those missing columns to your GROUP BY stmt and it should work here.

Aggregate formula works as - A field in the SELECT clause then it MUST be present in the GROUP BY

answered Jul 25 '18 at 12:58

Ak S

97
1
8

score 0 · Answer 2 · answered Jul 25 '18 at 13:39

0

As you already figured out, the GROUP BY is missing a few:

 ... 
 GROUP BY 
    year(sbi.dtmDelivered),
   month(sbi.dtmDelivered),
   o.strCompanyNodeName,
   p.strProductName,
   p.strProductType,
   o.strSalesRegionNodeGroup,
   o.strSalesRegionNodeName,
   o.strMasterSalesFirstNameNode,
   o.strMasterSalesLastNameNode;

answered Jul 25 '18 at 13:39

LukStorms

28,916
5
31
45

@James Notice that the group by is not on `sbi.dtmDelivered`, but on the year and month. Because I assume you would want to group on year and month, not the datetimes. – LukStorms Jul 25 '18 at 13:49
A simpified test snippet to understand the difference: `declare @T table (dt datetime); insert into @T values (GETDATE()),(GETDATE()-0.01); select year(dt)*100+month(dt) as yyyymm, count(*) as total from @T group by dt; select year(dt)*100+month(dt) as yyyymm, count(*) as total from @T group by year(dt), month(dt);` – LukStorms Jul 25 '18 at 14:16

BASIC SQL - Struggling with Group By Statements and Summing

2 Answers2