Grouping but with keeping all non-NULL values

Question

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.

You could take the MAX of each column within each group. – Scott Hunter Jun 10 '18 at 14:44 — Scott Hunter, Jun 10 '18 at 14:44

LukStorms · Accepted Answer · 2018-06-10T15:46:51.303

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

CodingYoshi · Answer 2 · 2018-06-10T15:32:25.760

0

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

edited Jun 10 '18 at 15:32

answered Jun 10 '18 at 14:49

CodingYoshi

25,467
4
62
64

score 0 · Answer 3 · answered Jun 10 '18 at 16:26

0

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.

answered Jun 10 '18 at 16:26

olleo

378
3
14

Actually I'm still pretty new to the whole thing and I just couldn't figure out what to search for. It's more for learning and personal use rather than efficiency. I do still appreciate your answer, however. ;) – Engreyight Jun 10 '18 at 17:28
@Engreyight Don't worry to much about this yet. Because this is ultimately a design choice. Does one want to put more work on the back-end and send less data to the front-end? Or should the front-end do all the heavy lifting? Such choice is sometimes decided case by case. Plus, aggregating is something databases are already good at. – LukStorms Jun 11 '18 at 08:27
@LukStorms Yes, he might as well render some html with his database. Oh wait, wouldn't it be nice if we could insert some css too based on the intolerance ? He's new to the playground and you're telling him it's ok to eat the sand... – olleo Jun 11 '18 at 10:58
1

@olleo Well, in his case doing this pivoting on the front-end would be fine I think. The extra amount of data send over the network won't be that excessive. But I do think that a doctrine of "Presentation must always be done in the presentation layer" is too strict. There are nuances, f.e. in [this old post](https://stackoverflow.com/questions/7510092/). Besides, SQL is also a form of presentation, but at a lower level. – LukStorms Jun 11 '18 at 11:50
@LukStorms First, your link is talking about calculation vs presentation. No nuances there. It's clearly separated. The rule is simple : don't do in your app what the database is trained for years to do (and vice versa). This is the basic of S.E. : separation of concerns. Second, you have a premature optimization thinking ("data send...") : that is like having cancer in S.E. – olleo Jun 11 '18 at 12:50

Grouping but with keeping all non-NULL values

3 Answers3