Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • 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
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!

 
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

Featured Post

Independent Software Vendors: 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!

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