Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSSQL Query Syntax Group and Sum

Posted on 2016-08-30
7
Medium Priority
?
64 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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