Using SUM() without grouping the results

Question

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.

Zane Bien · Accepted Answer · 2012-07-06T07:38:16.943

28

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.

edited Jul 06 '12 at 07:38

answered Jul 06 '12 at 07:31

Zane Bien

22,685
6
45
57

I get an error: `[HY000][1137] Can't reopen table: 'a'` – Rockin4Life33 Sep 11 '18 at 22:20
what if i have multiple categories like a.id,a.name,a.location ? will it still sum based on a.id ?? – Jul 12 '20 at 17:36

score 16 · Answer 2 · edited Sep 27 '19 at 09:24

16

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

--- 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

edited Sep 27 '19 at 09:24

Dino

7,779
12
46
85

answered Sep 11 '15 at 02:53

Valentin Petkov

1,570
18
23

1

I must be wrong, but did you seriously write out every single value from the database? That's not very programmatic and therefore not very helpful. – Anonymous Sep 13 '15 at 09:20
2

it is example, you can replace "FROM (...) with you complex query.. The second example is with PARTITION BY which is very useful when you want to do SUM() per MONTH for example – Valentin Petkov Sep 13 '15 at 15:27
Take a vote! My first time when the obvious right answer it's not the answer :) – Stefan Cantacuz Mar 10 '20 at 14:20
Edit: Missed mysql tag, but just checked and this code works with MySQL 8.0 – Stefan Cantacuz Mar 10 '20 at 14:28
yes Stefan if you add a proper index on "Partition ID" it is deadly, I like very much this approach than CROSS OVER and INNER JOIN – Valentin Petkov Mar 15 '20 at 21:57

score 6 · Answer 3 · answered Jul 06 '12 at 07:29

6

Join the original table to the sum with a subquery:

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

answered Jul 06 '12 at 07:29

Jon

428,835
81
738
806

1

@Bohemian, look closer, the subquery is not in the `SELECT` clause but is in fact part of the `FROM` clause which creates a cartesian product. It only executes once. – Zane Bien Jul 06 '12 at 07:35

score 0 · Answer 4 · answered Jul 06 '12 at 07:35

0

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

answered Jul 06 '12 at 07:35

Bohemian

412,405
93
575
722

I like answers that are the same as the accepted answer repeated -- almost as much as I like the answer that it was copied from! Same query, just not formatted neatly. – Rockin4Life33 Sep 11 '18 at 18:32

score 0 · Answer 5 · answered Apr 21 '21 at 03:32

0

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

answered Apr 21 '21 at 03:32

Khalid

343
3
16

Vivek mishra · Answer 6 · 2022-10-12T06:32:47.000

0

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;

edited Oct 12 '22 at 06:32

answered Oct 12 '22 at 06:24

Vivek mishra

1
2

Using SUM() without grouping the results

6 Answers6

Linked