Problems with Case statement and grouping.

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?
Lorna70Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
missed the sums . . . nice catch.
0
 
Lorna70Author Commented:
Thanks Scott - your solution worked perfectly.  Also, awarded 100 to ged235 because he was almost there.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.