How to query a Sum using an array with duplicate data?

Question

I currently have an Array in PHP with product numbers. Lets call that products. In my database I have a table containing combinations of product numbers and the price that belongs to that product.

Problem: My Array can hold duplicate entries if for instance article #1 is ordered twice.

If I use a query like

SELECT SUM(price) FROM articles WHERE article_number IN (products)

the duplicate entry of 1 get discarded. The query I am looking for gives the sum of 10 + 10 + 12.5 + 9.95.

Is there a way to do this within MySQL?

As a clarification to my data:

products = [1, 1, 2, 3];

articles|  article_number | price
__________________________________
        |  1              |  10.0
        |  2              |  12.5
        |  3              |  9.95

Thank you :)

You can use array unique function if you have issue with duplications — saurabh kamble
– saurabh kamble, Commented Jun 16, 2015 at 17:59

Rayhan Muktader · Accepted Answer · 2015-06-16 18:12:40Z

2

winmutt has a solid answer. However, if you don't have such a table to to join with then you could build your query like so:

select sum (p) from (
  (select price as p from articles where article_number = 1)
  union all
  (select price as p from articles where article_number = 1)
  union all
  (select price as p from articles where article_number = 2)
  union all
  (select price as p from articles where article_number = 3)
) s

answered Jun 16, 2015 at 18:12

Rayhan Muktader

2,1263 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

winmutt · Accepted Answer · 2015-06-16 18:09:41Z

1

If your products were in a table you could do a simple JOIN:

SELECT SUM(price) FROM articles JOIN products on article_number=product_number

answered Jun 16, 2015 at 18:09

winmutt

4052 silver badges7 bronze badges

Comments

Wouter van der Houven · Accepted Answer · 2015-06-16 18:35:27Z

0

I thought there might be an easier way of doing this in SQL. I solved it in PHP by querying all the prices of the array's products and then in iterating over my Array in PHP to sum up the price there.

answered Jun 16, 2015 at 18:35

Wouter van der Houven

4483 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to query a Sum using an array with duplicate data?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related