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
– S-Man, Commented Apr 18, 2019 at 9: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
– S-Man, Commented Apr 18, 2019 at 9:28
Yes, ITChap's answer solves my problem. I'd love to learn more about window functions though. — Aadit M Shah
– Aadit M Shah, Commented Apr 18, 2019 at 9:30

ITChap · Accepted Answer · 2019-04-18 09:22:40Z

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, 2019 at 9:22

ITChap

4,8421 gold badge32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amir Molaei · Accepted Answer · 2019-04-18 09:44:54Z

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, 2019 at 9:44

answered Apr 18, 2019 at 9:24

Amir Molaei

3,8601 gold badge20 silver badges22 bronze badges

3 Comments

Aadit M Shah Over a year ago

Could you explain what this query does? Perhaps show an example of an output.

Amir Molaei Over a year ago

Here is a good article: sqlshack.com/use-window-functions-sql-server

Aadit M Shah Over a year ago

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.

Collectives™ on Stack Overflow

SQL group by without aggregation

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related