SQL Question

Sum on destinct fields, kinda

[davemarks]

Hi guys, hoping you can help me find the best way to do this...

I have a shopping cart/catalog etc etc ecom site that i have built from scratch. I would like to add in hot products and most popular products, starting with the latter which is the easiest...

Orders are stored in one table and reference a cart of products in another table

Cart table has say id, orderID productCode qty color etc etc (each order has a row for each distinct product in its cart)

So i need to do a sum on the qty for each distinct productCode and pul out the top 10 say into a recordset

The words TOP 10, DISTINT and SUM() are jumping around in my head, but how to build a query so that it sums eash distinct productCode is the question

Any ideas? Thanks

Dave

[WebJoe]

From how I understand your problem this would be a good appproach for a solutions:

SELECT productCode, SUM(qty) As QtySum FROM Cart GROUP BY ProductCode
ORDER BY SUM(qty) DESC;

I can't think of an easy way to limit the result to the top 10 (with an SQL-statement). But you could loop the result into an array and limit the iterator to 10.

[davemarks]

Group By! Thats what I was missing, Thanks

No worries about the top 10, I'll just limit the loop.

I have a £50 SQL book, but its in Newquay and i'm in Bristol :( not much help

[WebJoe]

You're welcome, I'm glad I could help...

But £50! That's a lot of money for an SQL book. I've qorked quite a lot with SQl and I can't think of that much stuff that could be written about SQl that's worth £50.

My fav reference cost €9, but unfortunately it's in German and I didn't find it in English to recommend here...

[davemarks]

hehe, yeah i thought that when I brough it, but its definatly worth it.

It goes into all sort of detail about MS SQL aswell as just SQL as a language which is what i wanted at the time...

Again many thanks. It worked a treat btw ;)