0

Hello everyone,

I'm just wondering if the below is possible.

Let's say i have this table 

+---------+-------+
| item_id | price |
+---------+-------+
|      14 |  1500 |
|      74 |  1500 |
|      78 |  2500 |
+---------+-------+

And I'd like to select all item_id within this array (14,78,14)....

I thought I would be able to get the sum of all prices with this select:

mysql> select SUM(price) from items_db where item_id in (14,78,14);
+-------+
| price |
+-------+
|  4000 |
+-------+
1 row in set (0.00 sec)

But obviously it just skipped the price for the duplicate item_id.

So is there any way I could make this work?

Thanks in advance, Alex

Wracker
  • 589
  • 10
  • 32
  • No, `IN` isn't working that way. So - from where your array is derived? – Alma Do Aug 01 '14 at 14:08
  • well the array is generated by my small shop applicaiton. It's just a list of all selected items. – Wracker Aug 01 '14 at 14:10
  • then best way: do that in application. That is: select your prices where list of `item_id` is unique, then multiply each price for count of repetitions. There is a way to do summation in sql side, but that will require additional data structure (which will store your repetitions count together with selected values) – Alma Do Aug 01 '14 at 14:11
  • @fancyPants no, it should be done in _application_ (since you've misread what OP wants, obviously) – Alma Do Aug 01 '14 at 14:15
  • @AlmaDo Okay, okay, didn't read the numbers properly. Would've guessed that he reads an array from `[1]` instead of `[0]` or something like that. – fancyPants Aug 01 '14 at 14:17
  • I have tested this localy in my mysql (5.0) and it's obviously that the older version of Mysql is somehow summing the values differently that the newer versions of mysql. – Wracker Aug 01 '14 at 14:20

2 Answers2

0

You can't. You need to split the values that you receive, insert them on a temporary table and then do a join with the table that contains the values you wish to sum.

Wouldn't it be easy to do the select and then postprocess it with whatever language are you comfortable with?

Community
  • 1
  • 1
Sergi
  • 2,872
  • 2
  • 25
  • 24
  • OP doesn't have "string comma-separated list" of values. He has array which is used in `IN` explicitly – Alma Do Aug 01 '14 at 14:16
  • At the end it does not matter. He needs to take the values whatever form he receives them, insert them in a temporary table and do the join. – Sergi Aug 01 '14 at 14:34
0

ok got it... if you list duplicates in the IN(...) it wont work. At each row the database engine checks wether the rows matchs the IN (... ) list. So it is no going to read a row twice in such a statement

One way to read a given row several times is through a join... If you want to do such a sum() through SQL you have to join an intermediate (temporary) table. something like:

create temporary table itemlist (item_id);
insert into itemlist values (14),(78),(14);

select sum(price) from items_db inner join itemlist using (item_id);
+------------+
| sum(price) |
+------------+
|       5500 |
+------------+
user3899764
  • 1
  • 1
  • well, it would stand to reason. `where foo in (a,b,c)` is the equivalent of `where (foo=a) or (foo=b) or (foo=c)`. just because you've got `a` and `c` being identical doesn't mean mysql will suddenly include the same record twice. – Marc B Aug 01 '14 at 15:26
  • Just found another post related to this topic.. http://stackoverflow.com/questions/1109113/mysql-table-can-you-return-the-same-row-multiple-times-in-the-same-query it seems there is no other way just add another table for all the orders. – Wracker Aug 01 '14 at 15:26