Using SUM() without grouping the results
mysql, sum
Solution
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`.
Problem
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.