SQL group by without aggregation

Question

I'm using PostgreSQL, and I have the following table (simplified for the question):

CREATE TABLE SMSNotification (
    enduser int,
    message int,
    FOREIGN KEY (enduser) REFERENCES EndUser (id),
    FOREIGN KEY (message) REFERENCES Message (id)
);

Now, I'd like to batch send the messages. Hence, I'd like to group the notifications by message. The problem is that the GROUP BY operator in SQL aggregates rows. Therefore, I can't get the list of end users in each group:

SELECT enduser, message from SMSNotification GROUP BY message; /* Doesn't work */

I thought of solving this problem by first getting the list of all distinct messages:

SELECT DISTINCT message FROM SMSNotification;

Then for each message, getting the list of end users:

 SELECT enduser FROM SMSNotification WHERE message = current_message;

However, this seems inefficient because of the N+1 selects. Is there a better solution?

Example: Consider the following table:

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 1       |
| 2       | 1       |
| 3       | 1       |
| 1       | 2       |
| 2       | 2       |
| 1       | 3       |
| 3       | 3       |
+---------+---------+

Ideally, I'd like to get a list of tables as an output:

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 1       |
| 2       | 1       |
| 3       | 1       |
+---------+---------+

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 2       |
| 2       | 2       |
+---------+---------+

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 3       |
| 3       | 3       |
+---------+---------+

However, if I understand correctly, you can't get a list of tables as an output from an SQL query.

Could you please provide a sample input and expected output. I believe you are searching for window functions but I did not understand your use case completely. — S-Man, Apr 18 '19 at 09:17
What do you mean with "a list of tables"? A query always returns a result set that can be interpreted as one table. Maybe you want to aggregate the endusers into a list per message? Like @ITChap did in the answer below? — S-Man, Apr 18 '19 at 09:28
Yes, ITChap's answer solves my problem. I'd love to learn more about window functions though. — Aadit M Shah, Apr 18 '19 at 09:30

score 2 · Accepted Answer · answered Apr 18 '19 at 09:22

2

You could aggregate the users into an array like in

SELECT array_agg(enduser) AS endusers, message
FROM SMSNotification
GROUP BY message;

 endusers | message 
----------+---------
 {3}      |       3
 {2,3}    |       2
 {1,3}    |       1
(3 rows)

answered Apr 18 '19 at 09:22

ITChap

4,057
1
21
46

Amir Molaei · Answer 2 · 2019-04-18T09:44:54.117

1

I think you are looking for partition by command (a window function):

select row_number() over (partition by message order by enduser)
    as rn
    , message
    , enduser
from SMSNotification

A group by reduces the number of rows returned, by aggregating the non-grouped columns, while partition by does not affect the number of rows, it just specifies how the output must be displayed or calculated.

edited Apr 18 '19 at 09:44

answered Apr 18 '19 at 09:24

Amir Molaei

3,700
1
17
20

Could you explain what this query does? Perhaps show an example of an output. – Aadit M Shah Apr 18 '19 at 09:31
Here is a good article: https://www.sqlshack.com/use-window-functions-sql-server/ – Amir Molaei Apr 18 '19 at 09:49
Thanks for introducing me to window functions, but this doesn't help me though. If I was going to loop over the list to get the partitions, then I don't need the row number. I can group by the message. Hence, this is unnecessary work. – Aadit M Shah Apr 18 '19 at 10:30

SQL group by without aggregation

2 Answers2