Combine Duplicates from SQL Union into One Row Per ID

Question

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 |       | 8888@gmail.com                |         | 2023-04-02 20:05:53.186+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
 231 |       | 1234457@gmail.com             |         | 2023-04-02 20:01:17.629+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           4 |               0 |                  0 |             0
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               | +18125555555  | 1994/11/07 |           0 |               0 |                  0 |             2
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           0 |               1 |                  0 |             0
 229 |       | 7112@gmail.com                |         | 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_updated should be the most recent value from the last_updated column for that user
name, surname, phone_no, phone, birthday should be the most recent (newest last_updated) NOT NULL value, if such a value exists, and NULL otherwise
All the count fields 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 |       | 9999@gmail.com                |         | 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;

Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Apr 03 '23 at 23:41

Ben Kisow · Answer 1 · 2023-04-04T14:14:41.423

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

My bad, forgot to move the Max(last_updated) to the outer query, try now — Ben Kisow, Apr 04 '23 at 14:15
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Apr 09 '23 at 11:34

Combine Duplicates from SQL Union into One Row Per ID

1 Answers1