Mixing
Using SUM with DISTINCT
Using SUM with DISTINCT
TABLE:
item value
a 1
a 1
a 1
b 2
b 2
c 1
c 1
I want the result like this
SUM
4
It can be done by the following command :
SELECT SUM(value)
FROM (
SELECT DISTINCT ON (item) *
FROM TABLE
) s
But is there a simple command to do this?
Something like this:
SELECT SUM(value in DISTINCT(item)) FROM TABLE
In my case the value of the item is unique, the data is separated because of other column that is not unique in this table (which i haven't drawn).
– Jake
Jun 29 at 7:42
You will need two levels of nesting, but I think something like
select sum(value) from ( select item, min(value) as value from items group by item ) t would be more efficient– a_horse_with_no_name
Jun 29 at 7:45
select sum(value) from ( select item, min(value) as value from items group by item ) t
2 Answers
2
Leaving the fact that if an item has a different value the result set will be erroneous as pointed out in the comments, if you want to "simplify" it you could store it in a procedure:
item
CREATE OR REPLACE FUNCTION sumDiff() RETURNS INTEGER AS $$
DECLARE
ress integer;
BEGIN
SELECT SUM(value)
FROM (
SELECT DISTINCT ON (item) *
FROM itemvalues
) s
INTO ress;
RETURN ress;
END;
$$ LANGUAGE plpgsql;
SELECT sumDiff(); // returns 4
The correct command would be:
SELECT SUM(value)
FROM (SELECT DISTINCT ON (item) *
FROM TABLE
ORDER BY item
) s ;
The ORDER BY is important when using DISTINCT ON.
ORDER BY
DISTINCT ON
I cannot think of way of doing this without a subquery. In general, such data would be constructed from another query. The summation could be done better on the base tables, rather than the result. Hint: The data is not normalized.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
What if the values differ for a single item? E.g. (a,1),(a,2),(a,3),(a,4)?
– a_horse_with_no_name
Jun 29 at 7:21