Solved

Problems with Case statement and grouping.

Posted on 2012-03-15
4
232 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 39

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:
ScottPletcher 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 39

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now