CODEWITHSUNDEEP
sqlpostgresql
Cloud Artisans
Cloud Artisans

Reputation: 4136

How do I sum and group by across multiple identical tables in Postgres?

Due to a brain freeze, I'm having trouble writing the following SQL query. Let's say I have user purchases stored in identical tables like purchases1, purchases2, purchases3.

Each purchases table has these fields: integer id, integer user_id, numeric amount. Each user can have multiple purchases in multiple tables.

I want to get "total purchase amounts" for all users, in one query. If I was selecting from a single purchases table, I would do:

SELECT user_id, SUM(amount) FROM purchases1 GROUP BY user_id;

I know I can UNION three queries like this to end up with three sums for each user and then add those in a parent query to end up with the total. But that doesn't seem like the "right" way and gets especially messy if I have even more purchase tables. It seems like it should be possible to aggregate across the three tables as if they were one continuous table. What am I missing? Thanks in advance!

Upvotes: 0

Views: 258

Answers (1)

Jeremy Savage
Jeremy Savage

Reputation: 894

You can do something like:

WITH combined_purchases AS (
SELECT user_id, amount FROM purchases1
UNION
SELECT user_id, amount FROM purchases2
UNION
SELECT user_id, amount FROM purchases3)

SELECT user_id, SUM(amount) FROM combined_purchases GROUP BY user_id;

UNION all the data and then perform the same aggregation.

Upvotes: 1

Related Questions