July 26th, 2011, 06:33 PM
Subtable Data GROUPBY
I am working on a product sales system and there are two tables involved. The first holds a selection of sold products, the second a selection of extras that relate to these sold products.
So for example, the products table:
then the extras table:
product_id, int (value from products table)
Currently I do a select that groups by the price and decription to identify unique products on the invoice. This however is now not good enough, as the extras can change the value and makeup of the product.
I can create a function, to make sure the sets on the invoice by using SUM(extra_sell_price) + product_sell_price, which will make sure the overall values are the same, but what about making sure the descriptions are the same?
Can i somehow "sum()" using a group function the text descriptions to create one long string, which I could then MD5 hash to make sure they are the same?
What other ways could you suggest?
July 26th, 2011, 09:25 PM
Why would price make something unique? Surely you could have two (or more) products with the same price? Does not the product_id identify a unique item?
Would something like (untested!) do what you need?:
, p.product_sell_price + SUM(x.extra_sell_price) as Total
FROM products p
LEFT JOIN extras x
ON p.product_id = x.product_id
GROUP BY p.product_id
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc
July 27th, 2011, 02:12 AM
I am afraid it doesn't.
Imagine that the products are cars. The product Id identifies the base model, say 2l ford mondeo. Then the extras then include sports seats, a sunroof and AC.
There might be 10 cars on the order, all the same product id, 5 of which have the same extras, the others all have different extras.
On the invoice, I will group by product id, but I need also to "group by" the whole extras list.
....... Ahhhhhhhhh! Maybe I have just answered by own question. I will have a go at that! A subquery doing a select on a result set... Let me see.....