CODEWITHSUNDEEP
mysqlselectsum
Chris
Chris

Reputation: 115

MySQL combine select sum and select query

I want to sum 4 cells for each row based on a previous query that will reduce the selection down to the important rows.

Basically I need to combine those two queries (which work on their own):

SELECT columnx, SUM(`column1`+ `column2` + `column3` + `column4`) as total 
                FROM table GROUP BY columnx


SELECT * FROM (SELECT * FROM table ORDER BY columny DESC LIMIT 5) t 
                ORDER BY CASE
                when `pos` = 'PG' then 1
                when `pos` = 'SG' then 2
                when `pos` = 'SF' then 3
                when `pos` = 'PF' then 4
                else 5
                end asc

I tried to replace "table" with the second query but it's probably not the right way, since I'm getting errors here.

SELECT columnx, SUM(`column1`+ `column2` + `column3` + `column4`) as total FROM 
                  (( SELECT * FROM (SELECT * FROM table ORDER BY columny DESC LIMIT 5) t 
                  ORDER BY CASE
                  when `pos` = 'PG' then 1
                  when `pos` = 'SG' then 2
                  when `pos` = 'SF' then 3
                  when `pos` = 'PF' then 4
                  else 5
                  end asc) 
               GROUP BY columnx

Upvotes: 0

Views: 66

Answers (1)

cdaiga
cdaiga

Reputation: 4939

You were supposed to create an alias name for the block replacing table in your first query.

SELECT u.columnx, SUM(u.`column1`+ u.`column2` + u.`column3` + u.`column4`) as total 
FROM (SELECT t.* FROM (SELECT * FROM table ORDER BY columny DESC LIMIT 5) t 
ORDER BY CASE
 WHEN t.`pos`='PG' THEN 1
 WHEN t.`pos`='SG' THEN 2
 WHEN t.`pos`='SF' THEN 3
 WHEN t.`pos`='PF' THEN 4
ELSE 5
END ASC) u GROUP BY u.columnx

Upvotes: 1

Related Questions