Solved

MSSQL Query Syntax Group and Sum

Posted on 2016-08-30
7
54 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing SCCM 2016 4 44
efficient backup report for SQL Server 13 78
mssql 7 32
When are cursors useful? 8 55
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

738 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