Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problems with Case statement and grouping.

Posted on 2012-03-15
4
Medium Priority
?
243 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 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