Sum all rows returning from SELECT statement

Question

SELECT SUM(SELECT type.value
FROM type,item_type 
WHERE item_type.type_id = type.id AND item_type.type_id IN (4,7)
GROUP BY type.id)

What's wrong with this query? I would like to sum all rows coming from the internal query.

Because the internal query is using GROUP BY, this way SUM is affected by that statement. I want to sum all rows with **distinct id**, *displaying the sum from all distinct ids in one row*. — richardaum, Sep 10 '15 at 21:35
@Richard . . . Edit your question and provide sample data and desired results. — Gordon Linoff, Sep 10 '15 at 21:48

score 4 · Answer 1 · edited May 23 '17 at 12:29

You can't use SUM() function with subquerys. According to the manual the SUM() function returns the total sum of a numeric column. The way you were doing, was something like that: SELECT 1+50+30+10. Where is the table you were selecting the values? The sintax is:

SELECT SUM(column) FROM table

Take a look at: http://www.w3schools.com/sql/sql_func_sum.asp

The correct way is

SELECT t.id, SUM(t.value)
FROM type as t,
INNER JOIN item_type as it
ON it.type_id = t.id
WHERE it.type_id IN (4,7)
GROUP BY t.id

Consider to use JOIN sintax instead of multiple tables: SQL left join vs multiple tables on FROM line?

I edited and tried to explain better. Please try again! – Marcelo Machado Sep 11 '15 at 13:33 — Marcelo Machado, Sep 11 '15 at 13:33

score 1 · Answer 2 · answered Sep 10 '15 at 21:41

1

You should learn to use proper join syntax and table aliases:

SELECT SUM(t.value)
FROM type t JOIN
     item_type it
     ON it.type_id = t.id 
WHERE it.type_id IN (4,7);

If you want one row for each type.id, then you need a GROUP BY.

Your query doesn't work because subqueries are not allowed as arguments to aggregation functions. Even if they were, the context would be for a scalar subquery and your subquery is likely to return more than one row.

answered Sep 10 '15 at 21:41

Gordon Linoff

1,242,037
58
646
786

It should work, but GROUP BY is avoiding repeated values in `id` column. At the same time I would like to display the sum from all distinct ids in one row. – richardaum Sep 10 '15 at 21:43

score 0 · Answer 3 · answered Sep 10 '15 at 21:33

0

Just use SUM without internal query :

SELECT type.id, SUM(type.value)
FROM type,item_type 
WHERE item_type.type_id = type.id AND item_type.type_id IN (4,7)
GROUP BY type.id

answered Sep 10 '15 at 21:33

Roman Marusyk

23,328
24
73
116

[...] displaying the sum from all distinct ids in one row. :/ – richardaum Sep 10 '15 at 21:40
@Richard This query return one row with value of sum for distinct ids – Roman Marusyk Sep 10 '15 at 21:43
`item_type` is the relation between `type` table and `item` table. This relation is **n to n**, 'cause of this value can be duplicated. – richardaum Sep 10 '15 at 21:46
@Richard Please see your query, you select all values with distinct id and then try to get SUM of these values. The same work do the query above. Could you please more explain you problem? – Roman Marusyk Sep 10 '15 at 21:52

Sum all rows returning from SELECT statement

3 Answers3