CODEWITHSUNDEEP
sqlgroup-byalias
Seller Central
Seller Central

Reputation: 41

SQL group by and alias

Why do I get the error

Query Error: error: syntax error at or near "from"

in the following query:

select hi.d, hi.genre2, hi.total  
from (select t1.id as d, t1.genre as genre1, t2.genre as genre2, 
      CASE WHEN t2.genre = t1.genre 
      THEN t1.total
      ELSE 0
      END as TOTAL) as hi
from (select id, genre, SUM(spend) AS TOTAL
      from users group by id, genre) as t1
cross join users as t2
group by t1.id, t2.genre, t1.genre, t1.total))

Also, why do I need to include t1.genre and t1.total in the last group by (else I get an error)?

Upvotes: 0

Views: 47

Answers (1)

sacse
sacse

Reputation: 3744

try the following:

SELECT hi.d, 
       hi.genre2, 
       hi.TOTAL
FROM
(
    SELECT t1.id AS d, 
           T1.GENRE AS genre1, 
           t2.genre AS genre2,
           CASE
               WHEN t2.genre = t1.genre
               THEN t1.total
               ELSE 0
           END AS TOTAL
    FROM
    (
        SELECT id, 
               genre, 
               SUM(spend) AS TOTAL
        FROM users
        GROUP BY id, 
                 genre
    ) AS t1
    CROSS JOIN users AS t2
) hi;

I think 'hi' is not placed correctly in query and group by is not needed after cross join.

Upvotes: 1

Related Questions