CODEWITHSUNDEEP
phpmysql
lucAss
lucAss

Reputation: 3

MySQL SUM, ORDER BY, etc

I have this table:
Table

What I need: SUM(v+t+p) if m = '7' and SUM(v+t+p) if m = '8', and order it (by subtract (SUM(v+t+p) if m = '8')-SUM(v+t+p) if m = '7').

Upvotes: 0

Views: 61

Answers (1)

Uueerdo
Uueerdo

Reputation: 15951

This is a very general form of what you are looking for (probably):

SELECT [somestuff?] 
, SUM(IF([condition1], [calculation1], 0) AS X1
, SUM(IF([condition2], [calculation2], 0) AS X2
FROM theTable
WHERE [something]
GROUP BY [something else]
ORDER BY X1 - X2
;

Several sections are possibly optional for you.

To be more specific...

SELECT SUM(IF(m = '8', v+t+p, 0) AS sum8
, SUM(IF(m = '7', v+t+p, 0) AS sum7
FROM theTable
ORDER BY sum8-sum7  // This ORDER BY won't really do anything; 
                    // there is only one row since we didn't 
                    // GROUP BY anything
;

Edit: (for your back pocket to save some time, hopefully something useful)

create table thing
(   id int auto_increment primary key,
    n int not null,
    m int not null,
    v int not null,
    t int not null,
    p int not null
);

insert thing(n,m,v,t,p) values (2,8,0,0,0);
insert thing(n,m,v,t,p) values (14,8,0,0,0);
insert thing(n,m,v,t,p) values (48,7,123,123,123);
insert thing(n,m,v,t,p) values (48,8,12,1,2);
insert thing(n,m,v,t,p) values (390,8,0,0,0);

Edit:

SELECT n
, SUM(IF(m = '8', v+t+p, 0) AS sum8
, SUM(IF(m = '7', v+t+p, 0) AS sum7
, SUM(IF(m = '8', v+t+p, 0)
  - SUM(IF(m = '7', v+t+p, 0) AS sumDiff
FROM the_table
GROUP BY n
ORDER BY sumDiff
;

Upvotes: 1

Related Questions