Solved

Problems with Case statement and grouping.

Posted on 2012-03-15
4
235 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 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 100 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 69

Accepted Solution

by:
Scott Pletcher earned 150 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 40

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ISDATE() not working properly on my table? Any suggestions. 7 27
Help Required 3 96
TSQL - How to declare table name 26 30
SSAS Hierarchy with columns with folder names 10 14
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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