Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problems with Case statement and grouping.

Posted on 2012-03-15
4
Medium Priority
?
242 Views
Last Modified: 2012-03-15
Hi I need to display my data as per the following table:

Port_Description   Vessel_Name      LobstersKG      Crabs (KG)      Velvets(KG)
Aberdaron           Mary Rose                     3.5                      0                      0
                           Boy Paul                     2.3                    4.5                      0
Aberdeen                   Lady Eleanor             4.5                    2.3                      0
Aberffraw           Saorsa                             4.6                    2.5                      0
                        

I think I’m part of the way there but the following statement doesn’t give me the above table:

SELECT Port_Description, Vessel_Name, Common_Name,
      case
      when Common_Name = 'Lobsters' then ProductKiloQty
    end
    AS LobstersKG
      FROM FormData
      GROUP BY Port_Description, Vessel_Name, Common_Name, ProductKiloQty

It gives me this:

Port_Description      Vessel_Name      Common_Name      LobstersKG
Aberdaron                 Mary Rose            Lobsters                    3.5
                                 Boy Paul            Crabs                           NULL
                                 Boy Paul            Lobsters                    2.3
Aberdeen                         Lady Eleanor            Crabs                           NULL
                                 Lady Eleanor            Lobsters                    4.5
Aberffraw                 Saorsa                    Crabs                           NULL
                                 Saorsa                    Lobsters                    4.6

I tried adding to the case statement as follows:

SELECT Port_Description, Vessel_Name, Common_Name,
      case
      when Common_Name = 'Lobsters' then ProductKiloQty
    end AS LobstersKG
    case
      when Common_Name = 'Crabs' then ProductKiloQty
    end AS CrabsKG
      FROM FormData
      GROUP BY Port_Description, Vessel_Name, Common_Name, ProductKiloQty

But it didn’t work.  Please can someone tell me how I can achieve this?
0
Comment
Question by:Lorna70
  • 2
4 Comments
 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 total points
ID: 37725216
Need a comma to seperate columns.  Added else 0 so you won't get nulls. Remove the last two in the group by as I don't think you should be grouping by them.  Final query :

SELECT Port_Description, Vessel_Name, Common_Name,

      case
      when Common_Name = 'Lobsters' then ProductKiloQty else 0 end
    AS LobstersKG,

    case
      when Common_Name = 'Crabs' then ProductKiloQty else 0 end
    AS CrabsKG

      FROM FormData
      GROUP BY Port_Description, Vessel_Name
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 600 total points
ID: 37725273
SELECT
    Port_Description, Vessel_Name,
    SUM(CASE WHEN Common_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [Lobsters(KG)],
    SUM(CASE WHEN Common_Name = 'Crabs'    THEN ProductKiloQty ELSE 0 END) AS [Crabs(KG)],
    SUM(CASE WHEN Common_Name = 'Velvets'  THEN ProductKiloQty ELSE 0 END) AS [Velvets(KG)]
FROM FormData
GROUP BY
    Port_Description, Vessel_Name
ORDER BY --optional, of course
    Port_Description, Vessel_Name
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 37725411
missed the sums . . . nice catch.
0
 

Author Closing Comment

by:Lorna70
ID: 37725975
Thanks Scott - your solution worked perfectly.  Also, awarded 100 to ged235 because he was almost there.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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 this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

972 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