Menu
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.
418
SUM is used with a GROUP BY clause. The aggregate functions summarize the table data. Once the rows are divided into groups, the aggregate functions are applied in order to return just one value per group. It is better to identify each summary row by including the GROUP BY clause in the query resulst.
While all aggregate functions could be used without the GROUP BY clause, the whole point is to use the GROUP BY clause. That clause serves as the place where you'll define the condition on how to create a group. When the group is created, you'll calculate aggregated values.
Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column. we can use the following command to create a database called geeks.
SQL HAVING with SUM function example How the query works. First, for each order line item, SQL calculates the total amount using the SUM function. (The Total column alias is used for formatting the output). Third, the HAVING clause gets groups that have Total greater than 12000 .
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.
142
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 
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
