Solved

Help with Case Statement

Posted on 2009-07-07
4
164 Views
Last Modified: 2012-05-07
I only want to return 1 row per "Owner_Name". Right now my statement is displaying NULLs for each Report_GroupID that is present in the database;

select  owner_name as 'Name',
           case when report_groupid = 5 then sum(qty) end as 'RG 5',
           case when report_groupid = 1 or report_groupid = 2 then sum(qty) end as 'RG 1 & 2'
from check_item_record
where storeid = 14
and check_file_date = '7/6/2009'
group by owner_name, report_groupid,  qty

Here is how it is displaying:

Name            RG 5         RG 1 & 2
User1            15            Null
User1            Null         19

I want it to display like this:
Name            RG 5         RG 1 & 2
User1            15            19

Any ideas here?
0
Comment
Question by:mattkovo
[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
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24795818
Try like this:
+Move aggregate outside of case statement.
+Only group by owner_name.
(note you can use in for multiple values of same column)
select  owner_name as 'Name',
sum(case when report_groupid = 5 then qty end) as 'RG 5',
sum(case when report_groupid in (1,2) then qty end) as 'RG 1 & 2'
from check_item_record
where storeid = 14
and check_file_date = '7/6/2009'
group by owner_name

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24795822
SELECT Owner_name , sum(qty1) as 'RG 5', SUM(qty2) as 'RG 1 & 2'
FROM (
select  owner_name as 'Name',
           case when report_groupid = 5 then qty ELSE 0 end qty1 ,
           case when report_groupid = 1 or report_groupid = 2 then qty else 0 end as qty2  
from check_item_record
where storeid = 14
and check_file_date = '7/6/2009' )
A
group by owner_name
0
 

Author Closing Comment

by:mattkovo
ID: 31600675
That is perfect!  Thanks!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24795857
How about:

Select  owner_name as 'Name',
        sum(case when report_groupid = 5 then qty Else 0 end) as 'RG 5',
        sum(case when report_groupid in (1,2) then qty Else 0 end) as 'RG 1 & 2'
from    check_item_record
where   storeid = 14
        and check_file_date = '7/6/2009'
group by owner_name

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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