Need help with Group By query

Question

Post reply

Need help with Group By query

phurwitz

SSC-Addicted

Points: 456
More actions
July 10, 2007 at 3:05 pm

#117372

HI Everyone,
I have the query below. Its actually one part of a 3 part union query.
The data in the table is financial data for Revenue, COGS and Operating Expenses.
For revenue and operating expenses, I have different types (e.g. General Expenses, Salary, Other Expense etc).
For my output, I would like to group it by one of 3 types (Revenue, COGS, Operating Expenses).
The way it is written right now the query runs without any errors.
My first item in the select statement is to just insert a value for Type for every row.
I would like to use this 'Type' column in my group by statement.
When I replace 'Export_AccountGroup_2.GroupMeat_Name' with 'Type' in the group by statement I get an error saying "Invalid Column Name 'Type'"
Is it possible to do what I want? Can anyone help me figure this out?
SELECT Type='Operating Expenses', Export_Versions_2.Version_Name, Export_GL_AccountGroup_Data_2.FISCALYEAR,
SUM(Export_GL_AccountGroup_Data_2.M1 - Export_GL_AccountGroup_Data_2.BB) AS M1,
SUM(Export_GL_AccountGroup_Data_2.M2 - Export_GL_AccountGroup_Data_2.M1) AS M2,
SUM(Export_GL_AccountGroup_Data_2.M3 - Export_GL_AccountGroup_Data_2.M2) AS M3,
SUM(Export_GL_AccountGroup_Data_2.M4 - Export_GL_AccountGroup_Data_2.M3) AS M4,
SUM(Export_GL_AccountGroup_Data_2.M5 - Export_GL_AccountGroup_Data_2.M4) AS M5,
SUM(Export_GL_AccountGroup_Data_2.M6 - Export_GL_AccountGroup_Data_2.M5) AS M6,
SUM(Export_GL_AccountGroup_Data_2.M7 - Export_GL_AccountGroup_Data_2.M6) AS M7,
SUM(Export_GL_AccountGroup_Data_2.M8 - Export_GL_AccountGroup_Data_2.M7) AS M8,
SUM(Export_GL_AccountGroup_Data_2.M9 - Export_GL_AccountGroup_Data_2.M8) AS M9,
SUM(Export_GL_AccountGroup_Data_2.M10 - Export_GL_AccountGroup_Data_2.M9) AS M10,
SUM(Export_GL_AccountGroup_Data_2.M11 - Export_GL_AccountGroup_Data_2.M10) AS M11,
SUM(Export_GL_AccountGroup_Data_2.M12 - Export_GL_AccountGroup_Data_2.M11) AS M12
FROM Export_AccountGroup AS Export_AccountGroup_2 INNER JOIN
Export_GL_AccountGroup_Data AS Export_GL_AccountGroup_Data_2 ON
Export_AccountGroup_2.AccountGroup_ID = Export_GL_AccountGroup_Data_2.AccountGroup_ID INNER JOIN Export_Versions AS Export_Versions_2 ON Export_GL_AccountGroup_Data_2.Version_ID = Export_Versions_2.Version_ID
GROUP BY Export_AccountGroup_2.GroupMeat_Name, Export_Versions_2.Version_Name, Export_GL_AccountGroup_Data_2.FISCALYEAR
HAVING (Export_AccountGroup_2.GroupMeat_Name = N'General Expense') OR
(Export_AccountGroup_2.GroupMeat_Name = N'Depreciation') OR
(Export_AccountGroup_2.GroupMeat_Name = N'Amortization') OR
(Export_AccountGroup_2.GroupMeat_Name = N'Tax Provision') OR
(Export_AccountGroup_2.GroupMeat_Name = N'Salary Expense') OR
(Export_AccountGroup_2.GroupMeat_Name = N'Salary Related Expense') OR
(Export_AccountGroup_2.GroupMeat_Name = N'Other (Income)/Expense') OR
(Export_AccountGroup_2.GroupMeat_Name = N'Interest (Income)/Expense') OR
(Export_AccountGroup_2.GroupMeat_Name = N'(Gain)/Loss on Disposal of Assets')

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

1. Don't use EM for building queries. Especially complex ones.

Including ones containing GROUP BY clause.

2. Replace HAVING clause with WHERE.

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 2

The reason you can't use "Type" in your GROUP BY is that "Type" is an alias in your case, and the logical query processing order in SQL dictates that the GROUP BY is performed before the SELECT, so the alias hasn't been applied yet. While I can't test it at the moment, I don't see any reason that you can't put the literal value 'Operating Expenses' itself in the GROUP BY.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hello

As Sergiy suggests,

EM builds horrible queries

Use WHERE clause rather than HAVING, which operates after aggregation

Then tidy up the query by using shorter aliases to give you...

SELECT 
 [Type]='Operating Expenses', 
 ev2.Version_Name, 
 d2.FISCALYEAR, 
 SUM(d2.M1 - d2.BB) AS M1, 
 SUM(d2.M2 - d2.M1) AS M2, 
 SUM(d2.M3 - d2.M2) AS M3, 
 SUM(d2.M4 - d2.M3) AS M4, 
 SUM(d2.M5 - d2.M4) AS M5, 
 SUM(d2.M6 - d2.M5) AS M6, 
 SUM(d2.M7 - d2.M6) AS M7, 
 SUM(d2.M8 - d2.M7) AS M8, 
 SUM(d2.M9 - d2.M8) AS M9, 
 SUM(d2.M10 - d2.M9) AS M10, 
 SUM(d2.M11 - d2.M10) AS M11, 
 SUM(d2.M12 - d2.M11) AS M12

FROM Export_AccountGroup AS ag2 
INNER JOIN Export_GL_AccountGroup_Data AS d2 
 ON ag2.AccountGroup_ID = d2.AccountGroup_ID 
INNER JOIN Export_Versions AS ev2 
 ON d2.Version_ID = ev2.Version_ID

WHERE ag2.GroupMeat_Name IN ('General Expense', 'Depreciation', 'Amortization', 'Tax Provision', 
 'Salary Expense', 'Salary Related Expense', 'Other (Income)/Expense', 'Interest (Income)/Expense', 
 '(Gain)/Loss on Disposal of Assets')

GROUP BY ag2.GroupMeat_Name, ev2.Version_Name, d2.FISCALYEAR

...and finally, if you want to put [type] in the GROUP BY, and [type] is a constant (hard-coded as the first output column), I suspect it's not going to give you what you are expecting to see. In any case, try following David's advice for the reasons he gave ...

GROUP BY 'Operating Expenses', ag2.GroupMeat_Name, ev2.Version_Name, d2.FISCALYEAR

Hope this helps

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

phurwitz SSC-Addicted Points: 456 More actions · Answer 4

Hi Guys,

Thanks for you help so far. I tried the recommended method of putting 'Operating Expenses' in Group By and it gave an error saying that group by expressions must refer to column names that appear in the select list.

Any other ideas?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Not really - putting it in the GROUP BY in the way you infer won't actually change the result set because every row in the set will have the same value for [Type]. So the real question is - why do you want to do it? What would you expect to change in the result set?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Chris is right, if you supply a value directly (i.e. it is the same for all rows), you don't have to include it in GROUP BY clause.

phurwitz SSC-Addicted Points: 456 More actions · Answer 7

Thanks everyone,

I figured it out.

Based on everyone's ideas, I realized that if I just did my group by on the Version and FISCALYear columns and left my constant value in the select statement, I get exactly the data result I am looking for.

Thanks!