Solved

MSSQL Query Syntax Group and Sum

Posted on 2016-08-30
7
57 Views
Last Modified: 2016-08-30
Hi All...

The syntax of MSSQL drives us bonkers sometimes!  Uugh!

So, we have a VIEW in MSSQL, that has the following fields (there are more in the VIEW...but these are the *pertinent* ones, for this exercise):

FULL_TYPE      Varchar(1)
CATEG_COD   Varchar(10)
EXT_COST       Decimal(20,8)

When we run a 'test' query (no sums, no groupings), we get the following

EE_QUERY_SYNTAX_1.PNG
...which is *good*.  However, when we attempt to (a) use SUMS within the CASE statements, and (b) GROUP the results by CATEG_COD...which is ultimately what is required...we continually get errors.  An *example* of what we REALLY need follows:

EE_QUERY_SYNTAX_2.PNG
Can anyone shed some light, and please provide us the *correct* syntax...to make this work...please?  The "aggregate" functions tend to drive me batty.  I'd truly appreciate it!...Thanks!...Mark
0
Comment
Question by:datatechcorp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Accepted Solution

by:
Randy Peterson earned 500 total points
ID: 41777098
Put your sum around you case statements.   something like this:

sum(case when Full_Type in ('O','F') then EXT_COST Else 0 end) as XFER_OUT_EXT_COST

remember that when it doesn't meet your case criteria, you need the "Else 0" statement.
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 41777100
You can apply that logic to the rest of your case statements that you would like to Sum up.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 41777101
It would be easier if you posted selectable code instead of pictures of code but...

SELECT ...
   SUM(CASE WHEN FULL_TYPE IN ('O', 'F') THEN EXT_COST ELSE 0 END) AS XFER_OUT_EXT_COST,
   <repeat syntax for other columns>
FROM ...
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Closing Comment

by:datatechcorp
ID: 41777110
OH...my Lord...THANK YOU *soooo* doggone much Randy!  I *knew* it was something simple.  Syntax just drives me up the wall sometimes.  That worked *perfectly*!  Muchas Gracias!...Mark
0
 

Author Comment

by:datatechcorp
ID: 41777115
Hi Brian...

I was reading Randy's response and selected his solution, when I saw your response come through.  I apologize for acting too quickly.  Do you know a way, that I can "allocate" the points "after the fact"?  Your response is equally accurate.  Please let me know...and THANK YOU for responding!  It's very much appreciated!...Mark
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 41777179
Don't worry about it...glad you got your solution.
1
 

Author Comment

by:datatechcorp
ID: 41777195
Thank you Brian.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question