CODEWITHSUNDEEP
mysqlsum
Anonymous
Anonymous

Reputation: 3689

Using SUM() without grouping the results

I already read (this), but couldn't figure out a way to implement it to my specific problem. I know SUM() is an aggregate function and it doesn't make sense not to use it as such, but in this specific case, I have to SUM() all of the results while maintaining every single row.

Here's the table:

--ID-- --amount--
  1        23
  2        11
  3        8
  4        7

I need to SUM() the amount, but keep every record, so the output should be like:

--ID-- --amount--
  1        49
  2        49
  3        49
  4        49

I had this query, but it only sums each row, not all results together:

SELECT 
    a.id,
    SUM(b.amount)

FROM table1 as a 
JOIN table1 as b ON a.id = b.id
GROUP BY id

Without the SUM() it would only return one single row, but I need to maintain all ID's...

Note: Yes this is a pretty basic example and I could use php to do this here,but obviously the table is bigger and has more rows and columns, but that's not the point.

Upvotes: 34

Views: 114802

Answers (6)

Vivek mishra
Vivek mishra

Reputation: 1

Using Full Join -

case when you need sum of amount field from tableB and all data from tableA on behalf of id match.

  SELECT  a.amount, b.* FROM tableB b 
  full join (
      select id ,SUM(amount) as amount FROM tableA 
      where id  = '1' group by id
  ) a 
  on a.id = b.id where a.id ='1' or b.id  = '1' limit 1;

Upvotes: 0

Khalid
Khalid

Reputation: 371

in case someone else has the same problem and without joining we can do the following

select *
,totcalaccepted=(select sum(s.acceptedamount)  from cteresult s)
, totalpay=(select sum(s.payvalue)  from cteresult s)     
from cteresult  t
end

Upvotes: 0

Valentin Petkov
Valentin Petkov

Reputation: 1648

With MS SQL you can use OVER()

 select id, SUM(amount) OVER()
 from table1;

select id, SUM(amount) OVER()
from (
  select 1 as id, 23 as amount
  union all
  select 2 as id, 11 as amount
  union all
  select 3 as id, 8 as amount
  union all
  select 4 as id, 7 as amount
) A

enter image description here

--- OVER PARTITION ID

PARTITION BY which is very useful when you want to do SUM() per MONTH for example or do quarterly reports sales or yearly... (Note needs distinct it is doing for all rows) 

 select distinct id, SUM(amount) OVER(PARTITION BY id) as [SUM_forPARTITION]
 from (
     select 1 as id, 23 as amount
     union all
     select 1 as id, 23 as amount
     union all
     select 2 as id, 11 as amount
     union all
     select 2 as id, 11 as amount
     union all
     select 3 as id, 8 as amount
     union all
     select 4 as id, 7 as amount
) OverPARTITIONID

enter image description here

Upvotes: 17

Zane Bien
Zane Bien

Reputation: 23125

SELECT a.id, b.amount
FROM table1 a
CROSS JOIN
(
    SELECT SUM(amount) amount FROM table1
) b

You need to perform a cartesian join of the value of the sum of every row in the table to each id. Since there is only one result of the subselect (49), it basically just gets tacked onto each id.

Upvotes: 29

Bohemian
Bohemian

Reputation: 425023

This does just one sum() query, so it should perform OK:

SELECT a.id, b.amount
FROM table1 a
cross join (SELECT SUM(amount) as amount FROM table1 AS amount) b

Upvotes: 0

Jon
Jon

Reputation: 437376

Join the original table to the sum with a subquery:

SELECT * FROM table1, (SELECT SUM(amount) FROM table1 AS amount) t

Upvotes: 6

Related Questions