Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

MSSQL Query Syntax Group and Sum

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
datatechcorp
Asked:
datatechcorp
  • 3
  • 2
  • 2
1 Solution
 
Randy PetersonCommented:
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
 
Randy PetersonCommented:
You can apply that logic to the rest of your case statements that you would like to Sum up.
0
 
Brian CroweDatabase AdministratorCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
datatechcorpAuthor Commented:
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
 
datatechcorpAuthor Commented:
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
 
Brian CroweDatabase AdministratorCommented:
Don't worry about it...glad you got your solution.
1
 
datatechcorpAuthor Commented:
Thank you Brian.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now