WHERE and SUM issue

[Twitch]

Hello Gurus,

 

I've tried all day to get this query right, but no luck so I finally had to break down and ask for help.  I'm using MySQL 5.0

 

I have a reservation system where products can be reserved.  The reservation_items table is like so:

id  |  specialProductID  |  productID  | quantity

1              101                    0              2

2                0                    102            3

 

The specialProductID is actually a bundle of products with their own quantities tied to a special_items table like so:

  specialProductID  |  productID  | quantity

      101                        103            2

      101                        104            1

 

My goal is to display the top 3  reserved products based on highest quantity individually.  So in the case above my repeat region would show this order:

Top products

103 quantity of 4

102 quantity of 3

104 quantity of 2

 

When the reservation item is not part of a bundle then the quantity in the reservation_items table is the quantity, if it's a special product (a bundle of products) the reservation_items quantity is multiplied by the special product quantity.  I have gotten my query close, but not quite what I need.

 

SELECT 
  SUM(productQuantity * specialQuantity) AS Specials_Quantity,
  SUM(productQuantity) AS Products_Quantity
FROM
  reservations
  INNER JOIN reservation_items ON (reservations.reservationID = reservation_items.reservationID)
  INNER JOIN special_items ON (reservation_items.specialProductID = special_items.specialProductID)
  INNER JOIN products ON (special_items.productID = products.productID)
GROUP BY
  productQuantity
  ORDER BY Specials_Quantity, Bottles_Quantity

 

I believe I also need to add a WHERE reservation_items.specialProductID<>0 to the Special_Quantity SUM and WHERE reservation_items.specialProductID=0 to the Bottles_Quantity but I don't know the proper syntax.

 

Any help would be greatly appreciated.

 

Thanks in advance,

Twitch

 

 

[Twitch]

Bummer, no replies.  I've been trying to find an answer but still nothing.  I have found out that WHERE won't work with SUM and I'm not sure if HAVING is what I'm supposed to do.

 

Back to Google... :-\

[fenway]

And why didn't HAVING work?

[Twitch]

Hi Fenway, thanks for the reply.  I wasn't sure what HAVING was, but since posting this the difference between WHERE and HAVING was explained to me like so:

 

"It's like asking someone to fetch blue balls from a dark room filled with different colored balls and cubes. You can tell them to SELECT balls FROM room WHERE shape='round' HAVING color='blue'. While performing the query (fetching the items), the shape is used to limit the number of retrieved items, but the color is not known until after the query has finished and the balls are brought out into the light, and as such they have to be discarded after everything is retrieved."

 

I haven't had a chance to go back and reexamine this query.  I'm just now getting into more complicated queries and using aliases etc.

 

Once again, thanks a bunch for the reply.  Hopefully, I'll get to re-write this query tonight.

 

Sincerely,

Twitch