CODEWITHSUNDEEP
sql
Engreyight
Engreyight

Reputation: 13

Grouping but with keeping all non-NULL values

Let's say I have the table: 

 ID |  Name | Intolerance
  1 |   Amy |     Lactose
  2 | Brian |     Lactose
  3 |   Amy |      Gluten

And I run this SQL query: 

SELECT 
    Name,
    CASE 
       WHEN Intolerance = 'Lactose' 1
    END AS Lactose,
    CASE 
       WHEN Intolerance = 'Gluten' 1
    END AS Gluten
FROM 
    Table

I get: 

  Name   | Lactose | Gluten
  -------+---------+--------
  Amy    |    1    |
  Amy    |         |   1
  Brian  |    1    |

But if I try to add "GROUP BY Name", Amy won't have a 1 in both columns, because GROUP BY only selects the last row of each Name. What I want to get instead is this: 

 Name | Lactose | Gluten
------+---------+---------
  Amy |       1 |      1
Brian |       1 |

How can I get that? Is there perhaps a more efficient way to summarize who's allergic to what from the same input? Thanks in advance.

Upvotes: 1

Views: 1796

Answers (3)

olleo
olleo

Reputation: 376

I feel that each time I encounter a question like that, it's because a no proper amount of thinking on design was allowed to the project.

To put it simply : you are trying to move data to columns. This is what your application layer is for ! Not the database. People tend to mix what databases are for with what application / UI layer and vice versa are for !

And each time it happens, I see people reaping their mind to answer because that's the point here : answer the question no matter what. Don't question what the OP want to do, give him the answer...

Sorry for that, I am just a little bit pissed.

My solution : Keep your original query and do the aesthetic on your UI / application layer side. You probably have a IList inside each Person. Just fill them and give the UI the opportunity to display them however it wants. Because that's what you're asking the database to do : aesthetics.

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

When using a GROUP BY then the aggregate functions can be used for columns that aren't in the GROUP BY.

In this case I assume you want to use MAX, to get only a 1 or a NULL.

SUM or COUNT can also be used to surround a CASE WHEN.
But then those would return a total. 

SELECT 
 Name,
 MAX(CASE WHEN Intolerance = 'Lactose' THEN 1 END) AS Lactose,
 MAX(CASE WHEN Intolerance = 'Gluten' THEN 1 END) AS Gluten
FROM Table
GROUP BY Name
ORDER BY Name

Or if you don't want to see NULL's?
Then let the CASE return a varchar instead of a number.

SELECT 
 Name,
 MAX(CASE WHEN Intolerance = 'Lactose' THEN '1' ELSE '' END) AS Lactose,
 MAX(CASE WHEN Intolerance = 'Gluten' THEN '1' ELSE '' END) AS Gluten
FROM Table
GROUP BY Name
ORDER BY Name

Upvotes: 4

CodingYoshi
CodingYoshi

Reputation: 26989

I think what you need is the sum of the number of number of intolerances for each person. Also, put a ELSE so the value is 0 or 1: 

SELECT 
     Name,
     SUM(CASE WHEN Intolerance = 'Lactose' THEN 1 ELSE 0 END) AS Lactose,
     SUM(CASE WHEN Intolerance = 'Gluten' THEN 1 ELSE 0 END) AS Gluten
FROM Table
GROUP BY Name
ORDER BY Name

Upvotes: 0

Related Questions