How to create alias from all columns in sql?

Question

The goal of the query here was simplified, but it represents a complex one that I want to select all users fields from the subquery plus computing a SUM. So, this is an example only.

I'm doing a subquery because of a problem with SUM duplicate rows. Like recommended to do with this answer: https://stackoverflow.com/a/7351991/255932

But the problem is that subquery also selects a column "rating" from the table ratings and I can't select all users fields unless describing all users columns on parent select.

SELECT id, name, x, y, z ..., SUM(rating)

FROM
   (SELECT users.*, ratings.rating
    FROM users
    INNER JOIN ratings ON
    users.id = ratings.user_id
   )

GROUP BY users.id

I would like to know if there is a way to replace (id, name, x, y, z, ...) with a simple (users.*).

Answer 1

Actually, there are two very simple ways.

If users.id is the primary key:

SELECT u.*, sum(r.rating) AS total
FROM   users   u
JOIN   ratings r ON r.user_id = u.id
GROUP  BY u.id;

You need Postgres 9.1 or later for this to work. Details in this closely reated answer:
PostgreSQL - GROUP BY clause

If users.id is at least unique:

SELECT u.*, r.total
FROM   users u
JOIN  (
   SELECT user_id, sum(rating) AS total
   FROM   ratings
   GROUP  BY 1
    ) r ON r.user_id = u.id;

Works with any version I know of. When retrieving the whole table or large parts of it, it's also generally faster to group first and join later.

Answer 2

Kind of, but not really. There is a workaround, but you have to approach your subquery differently.

SELECT (c.users).*, SUM(c.rating)

FROM
   (SELECT users, ratings.rating
    FROM users
    INNER JOIN ratings ON
    users.id = ratings.user_id
   ) c

GROUP BY c.users;