CODEWITHSUNDEEP
phpmysqlsum
user3305539
user3305539

Reputation: 123

sum of values from an array with duplicates

Trying to add sum of values from query, but not sure how to get it to add all the values at the moment it's just finding unique values from the list but it should be adding all of them.

eg price of item 11 is £1 price of item 35 is £2

Am trying to add values of 11 and 35 and 35 ie (£1+£2+£2) to get a value of £5

the query I am using is obviously off the mark as it's returning £3 so it's only adding the unique values attached to the unique items

SELECT sum(price) FROM prices where item_id IN(11,35,35)

any help much appreciated

Upvotes: 1

Views: 65

Answers (2)

Fluffeh
Fluffeh

Reputation: 33512

You need to force a cartesian join on the tables and you can do that like this:

select
    sum(yt.price) as TotalPrice
from
    yourTable yt
        join (
            select
                11 as ID
            from dual
            union all
            select
                35 as ID
            from dual
            union all
            select
                35 as ID
            from dual
        ) sel
            on yt.ID=sel.ID

You create a subquery that has multiple records for the IDs you want and join it to the actual table you want to pull the data from.

The other thing you could do would be to pull back the individual item prices and sort it out in an array/object where you can do the maths to get the price in a much more detailed manner.

The other thing you could do quite simply with an array (or however you are storing your IDs that you want to tally the price for) would be to do a variable amount of unions inside an outer query - similar really to what is above, but will look very different:

select
    sum(sel.price)
from
    (
        select price from yourTable where ID=11
        union all
        select price from yourTable where ID=35
        union all
        select price from yourTable where ID=35
    ) sel

It's pretty much the same but depending on the rest of the columns you pull into the query, might maybe suit better.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You need to do this using a join. The where clause does not increase the number of rows. So:

select sum(price)
from (select 11 as item union all select 35 union all select 35
     ) i join
     prices p
     on i.item = p.item;

Upvotes: 0

Related Questions