CODEWITHSUNDEEP
mysqlsqldatabaseaggregate-functions
Basel
Basel

Reputation: 369

How to add the result of multiple aggregation functions in the same query?

I have a query that has a math calculations and aggregate functions as following:

SELECT u.username, u.id, COUNT(t.tahmin) AS tahmins_no, 
       SUM(t.result = 1) AS winnings,
       SUM(t.result = 2) AS loses,
       sum(case when t.tahmin = 1 and t.result = 1 then 1 else 0 end) * 1 as ms1,
       sum(case when t.tahmin = 2 and t.result = 1 then 1 else 0 end) * 3 as ms0,
       sum(case when t.tahmin = 3 and t.result = 1 then 1 else 0 end) * 1 as ms2,
       sum(case when t.tahmin = 4 and t.result = 1 then 1 else 0 end) * 2 as alt,
       sum(case when t.tahmin = 5 and t.result = 1 then 1 else 0 end) * 2 as ust,
       sum(case when t.tahmin = 6 and t.result = 1 then 1 else 0 end) * 3 as tg_0_1,
       sum(case when t.tahmin = 7 and t.result = 1 then 1 else 0 end) * 2 as tg_2_3,
       sum(case when t.tahmin = 8 and t.result = 1 then 1 else 0 end) * 4 as tg_4_6,
       sum(case when t.tahmin = 9 and t.result = 1 then 1 else 0 end) * 20 as tg_7,
       sum(case when t.tahmin = 10 and t.result = 1 then 1 else 0 end) * 1 as kg_var,
       sum(case when t.tahmin = 11 and t.result = 1 then 1 else 0 end) * 1 as kg_yok
       sum(ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok) as total    
FROM users u 
LEFT JOIN tahminler t ON u.id = t.user_id 
LEFT JOIN matches_of_comments mc ON t.match_id = mc.match_id
WHERE MONTH(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) = 01 AND 
      YEAR(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) =  2014 AND flag=1
GROUP BY u.id 
HAVING tahmins_no > 0
ORDER BY total DESC

The query is working very well and I get the expected results the only problem is when I add the following line to the query :

sum(ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok) as total

I want to order my columns by total I want to know is that line is correct or not? is the syntax of it correct or not?

Upvotes: 0

Views: 87

Answers (2)

Bohemian
Bohemian

Reputation: 424973

Your special sum is not actually a sum but a simple addition, which should be made using a wrapper query.

The sums of your query can be simplified by using the fact that in mysql true is 1 and false is 0 (as you are already doing for winnings and loses).

You also have a defect in your group by clause: You must list all non-aggregate columns (ie username and id) otherwise you will get non-standard behaviour (ie the wrong result). Note how this too can also be expressed more simply.

Try this:

SELECT *, ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok as total FROM (
  SELECT
    u.username,
    u.id,
    COUNT(t.tahmin) AS tahmins_no, 
    SUM(t.result = 1) AS winnings,
    SUM(t.result = 2) AS loses,
    sum(t.tahmin = 1 and t.result = 1) * 1 as ms1,
    sum(t.tahmin = 2 and t.result = 1 then 1 else 0 end) * 3 as ms0,
    sum(t.tahmin = 3 and t.result = 1) * 1 as ms2,
    sum(t.tahmin = 4 and t.result = 1) * 2 as alt,
    sum(t.tahmin = 5 and t.result = 1) * 2 as ust,
    sum(t.tahmin = 6 and t.result = 1) * 3 as tg_0_1,
    sum(t.tahmin = 7 and t.result = 1) * 2 as tg_2_3,
    sum(t.tahmin = 8 and t.result = 1) * 4 as tg_4_6,
    sum(t.tahmin = 9 and t.result = 1) * 20 as tg_7,
    sum(t.tahmin = 10 and t.result = 1) * 1 as kg_var,
    sum(t.tahmin = 11 and t.result = 1) * 1 as kg_yok
  FROM users u 
  LEFT JOIN tahminler t ON u.id = t.user_id 
  LEFT JOIN matches_of_comments mc ON t.match_id = mc.match_id
  WHERE MONTH(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) = 01
  AND YEAR(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) =  2014
  AND flag=1
  GROUP BY 1, 2
  HAVING tahmins_no > 0) x
ORDER BY total DESC

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

You're trying to get a SUM of SUMs (noz needed in that case) and you want to reuse an alias, both are not allowed in Standard SQL (without Derived Tables).

If it's only for sorting you simply need to 

ORDER BY ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok DESC

If you want to display the TOTAL in your SELECT list you need to repeat the calculation:

   sum(case when t.tahmin = 1 and t.result = 1 then 1 else 0 end) * 1 +
   sum(case when t.tahmin = 2 and t.result = 1 then 1 else 0 end) * 3 +
   ....
   sum(case when t.tahmin = 11 and t.result = 1 then 1 else 0 end) * 1 as TOTAL

or better use a Derived Table:

SELECT username, id, tahmins_no, 
       winnings,
       loses,
       ms1,
       ms0,
       ...
       kg_yok,
       ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok as TOTAL
FROM 
  (
    your existing query (without ORDER BY)
  ) as dt
ORDER BY TOTAL DESC

Upvotes: 1

Related Questions