I have the following SQL script(of which the result is displayed under the script). The issue I am having is that I need to add up the quantity on the invoice. The quantity works fine when all the products on the invoice are different. When there is a product that appears twice on the invoice, the result is incorrect. Any help appreciated.
Asked
Active
Viewed 39 times
1 Answers
0
The DISTINCT keyword acts on all columns you select.
A new product introduces a difference which makes it no longer distinct. Hence the extra row(s).
Where you had:
Order Product Total
1 Toaster $10
2 Chair $20
And another item is added to order 1:
Order Product Total
1 Toaster $99
1 Balloon $99 -- Yes that's a $89 balloon!
2 Chair $20
The new row (balloon) is distinct and isn't reduced into the previous row (toaster).
To make is distinct again, don't select the product name:
Order Total
1 $99
2 $20
Uniqueness kicks in and everyone's happy!
If you can remove the column from the select list that's "different", you should get the results you need.
Adrian Lynch
- 8,237
- 2
- 32
- 40
-
thanks for explaining Adrian. the thing is I want to group the two products so it only shows one. BUT I want to show that it is two in the units. – yoda Feb 25 '16 at 03:57
-
Take a look at `group_concat` - It'll put the products into a list and hence in one row. – Adrian Lynch Feb 25 '16 at 03:59
-
sorry for not clarifying but I'm on SQL 2008 so I don't have that function. is there an equivalent? – yoda Feb 25 '16 at 04:34
-
No worries, I was in MySQL mode! – Adrian Lynch Feb 25 '16 at 04:35
-
So you could try and emulate `group_concat`: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server But that's nasty. – Adrian Lynch Feb 25 '16 at 04:37
-
don't think I will go there. – yoda Feb 25 '16 at 08:46