Alk
Reputation: 5557
Combine Duplicates from SQL Union into One Row Per ID
I have the following query which collates data from a few tables and produces results in the following format:
id | name | email | surname | last_updated | phone | phone_no | birthday | order_count | email_col_count | review_track_count | loyalty_count
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
232 | | [email protected] | | 2023-04-02 20:05:53.186+00 | | | 1994/11/07 | 0 | 0 | 0 | 1
231 | | [email protected] | | 2023-04-02 20:01:17.629+00 | | | 1994/11/07 | 0 | 0 | 0 | 1
230 | | [email protected] | | 2023-04-02 19:58:44.432+00 | | | | 4 | 0 | 0 | 0
230 | | [email protected] | | 2023-04-02 19:58:44.432+00 | | +18125555555 | 1994/11/07 | 0 | 0 | 0 | 2
230 | | [email protected] | | 2023-04-02 19:58:44.432+00 | | | | 0 | 1 | 0 | 0
229 | | [email protected] | | 2023-04-02 19:45:25.49+00 | | +19098003700 | 1994/01/11 | 0 | 0 | 0 | 1
The problem is, I want to have just one row for each id with the data collated as follows:
last_updatedshould be the most recent value from thelast_updatedcolumn for that username,surname,phone_no,phone,birthdayshould be the most recent (newestlast_updated) NOT NULL value, if such a value exists, and NULL otherwise- All the
countfields should be a sum of the respective count field for the given user.
Therefore, for 230 the row should like this:
id | name | email | surname | last_updated | phone | phone_no | birthday | order_count | email_col_count | review_track_count | loyalty_count
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
230 | | [email protected] | | 2023-04-02 19:58:44.432+00 | | +18125555555 | 1994/11/07 | 4 | 1 | 0 | 2
How can I alter my query to achieve this?
SELECT id,
name,
email,
surname,
last_updated,
phone,
phone_no,
birthday,
Sum(order_count) AS order_count,
Sum(email_col_count) AS email_col_count,
Sum(review_track_count) AS review_track_count,
Sum(loyalty_count) AS loyalty_count
FROM (
SELECT u.id,
u.name,
u.email,
u.surname,
'order_user' AS type,
Max(u."updatedAt") AS last_updated,
Max(ord."phoneNumber") AS phone,
NULL AS phone_no,
NULL AS birthday,
Count(DISTINCT ord.id) AS order_count,
0 AS email_col_count,
0 AS review_track_count,
0 AS loyalty_count
FROM users u
JOIN orders ord
ON u.id=ord."orderUserId"
AND ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6)
GROUP BY u.id,
type
UNION
SELECT u.id,
u.name,
u.email,
u.surname,
'email_collection' AS type,
Max(u."updatedAt") AS last_updated,
NULL AS phone,
NULL AS phone_no,
NULL AS birthday,
0 AS order_count,
Count(DISTINCT col.id) AS email_col_count,
0 AS review_track_count,
0 AS loyalty_count
FROM users u
JOIN "userEmailCollections" col
ON u.id=col."userId"
AND col."restaurantId" = 6
GROUP BY u.id,
type
UNION
SELECT u.id,
u.name,
u.email,
u.surname,
'review_track' AS type,
Max(u."updatedAt") AS last_updated,
NULL AS phone,
NULL AS phone_no,
NULL AS birthday,
0 AS order_count,
0 AS email_col_count,
Count(DISTINCT rev.id) AS review_track_count,
0 AS loyalty_count
FROM users u
JOIN "reviewTracks" rev
ON u.email=rev."email"
AND rev."restaurantId" = 6
GROUP BY u.id,
type
UNION
SELECT u.id,
u.name,
u.email,
u.surname,
'loyalty_campaign_redemption' AS type,
Max(u."updatedAt") AS last_updated,
NULL AS phone,
Max(loyy."phoneNo") AS phone_no,
Max(loyy.birthday) AS birthday,
0 AS order_count,
0 AS email_col_count,
0 AS review_track_count,
Count(DISTINCT loyy.id) AS loyalty_count
FROM users u
JOIN "loyaltyCampaignRedemptions" loyy
ON u.id=loyy."userId"
AND loyy."restaurantId" = 6
GROUP BY u.id,
type ) AS subquery
GROUP BY id,
name,
email,
surname,
type,
last_updated,
phone,
phone_no,
birthday
ORDER BY last_updated DESC limit 50;
Upvotes: -1
Views: 86
Answers (1)
Ben Kisow
Reputation: 46
You could use FIRST_VALUE, for example, try the below code:
SELECT
id
,name
,email
,surname
,Max(last_updated) as last_updated
,phone
,phone_no
,birthday
,Sum(order_count) AS order_count
,Sum(email_col_count) AS email_col_count
,Sum(review_track_count) AS review_track_count
,Sum(loyalty_count) AS loyalty_count
FROM
(
SELECT id,
FIRST_VALUE(name) OVER (PARTITION BY id ORDER BY last_updated DESC) as name,
FIRST_VALUE(email) OVER (PARTITION BY id ORDER BY last_updated DESC) as email,
FIRST_VALUE(surname) OVER (PARTITION BY id ORDER BY last_updated DESC) as surname,
last_updated,
FIRST_VALUE(phone) OVER (PARTITION BY id ORDER BY last_updated DESC) as phone,
FIRST_VALUE(phone_no) OVER (PARTITION BY id ORDER BY last_updated DESC) as phone_no,
FIRST_VALUE(birthday) OVER (PARTITION BY id ORDER BY last_updated DESC) as birthday,
order_count,
email_col_count,
review_track_count,
loyalty_count
FROM (
SELECT u.id,
u.name,
u.email,
u.surname,
'order_user' AS type,
Max(u."updatedAt") AS last_updated,
Max(ord."phoneNumber") AS phone,
NULL AS phone_no,
NULL AS birthday,
Count(DISTINCT ord.id) AS order_count,
0 AS email_col_count,
0 AS review_track_count,
0 AS loyalty_count
FROM users u
JOIN orders ord
ON u.id=ord."orderUserId"
AND ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6)
GROUP BY u.id,
type
UNION
SELECT u.id,
u.name,
u.email,
u.surname,
'email_collection' AS type,
Max(u."updatedAt") AS last_updated,
NULL AS phone,
NULL AS phone_no,
NULL AS birthday,
0 AS order_count,
Count(DISTINCT col.id) AS email_col_count,
0 AS review_track_count,
0 AS loyalty_count
FROM users u
JOIN "userEmailCollections" col
ON u.id=col."userId"
AND col."restaurantId" = 6
GROUP BY u.id,
type
UNION
SELECT u.id,
u.name,
u.email,
u.surname,
'review_track' AS type,
Max(u."updatedAt") AS last_updated,
NULL AS phone,
NULL AS phone_no,
NULL AS birthday,
0 AS order_count,
0 AS email_col_count,
Count(DISTINCT rev.id) AS review_track_count,
0 AS loyalty_count
FROM users u
JOIN "reviewTracks" rev
ON u.email=rev."email"
AND rev."restaurantId" = 6
GROUP BY u.id,
type
UNION
SELECT u.id,
u.name,
u.email,
u.surname,
'loyalty_campaign_redemption' AS type,
Max(u."updatedAt") AS last_updated,
NULL AS phone,
Max(loyy."phoneNo") AS phone_no,
Max(loyy.birthday) AS birthday,
0 AS order_count,
0 AS email_col_count,
0 AS review_track_count,
Count(DISTINCT loyy.id) AS loyalty_count
FROM users u
JOIN "loyaltyCampaignRedemptions" loyy
ON u.id=loyy."userId"
AND loyy."restaurantId" = 6
GROUP BY u.id,
type ) AS subquery
GROUP BY id
ORDER BY last_updated DESC limit 50;
)
GROUP BY
id
,name
,email
,surname
,last_updated
,phone
,phone_no
,birthday
Upvotes: 0
Related Questions
- How to concatenate text from multiple rows into a single text string in SQL Server
- How to return only the Date from a SQL Server DateTime datatype
- Insert into ... values ( SELECT ... FROM ... )
- Import SQL dump into PostgreSQL database
- How do I UPDATE from a SELECT in SQL Server?
- How to request a random row in SQL?
- How can I get column names from a table in SQL Server?
- How to select the nth row in a SQL database table?
- Can I concatenate multiple MySQL rows into one field?
- SQL Update from One Table to Another Based on a ID Match