Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: GROUP BY not working?

    I'm having a really odd problem.. this is basic SQL, so it should be easy for most of you. For some strange reason, the quantity is not being summed up. Any idea why this would be happening? Everything else is grouped. The SQL is below, and some of the results are below that (you can see that it SHOULD be grouping them properly).

    SELECT TOP 100 PERCENT dbo.cp_elements.campaign_id, AS campaign_name, dbo.cp_elements.item_no,,
    dbo.cp_orderables.est_qty, dbo.cp_orderables.qty_increment, SUM(dbo.cp_order_detail.qty) as qty, dbo.cp_attribs.price
    FROM dbo.cp_orderables INNER JOIN
    dbo.cp_elements ON dbo.cp_orderables.element_id = dbo.cp_elements.element_id INNER JOIN
    dbo.cp_attribs ON dbo.cp_orderables.orderable_id = dbo.cp_attribs.orderable_id INNER JOIN
    dbo.cp_order_detail ON dbo.cp_attribs.attrib_id = dbo.cp_order_detail.attrib_id INNER JOIN
    dbo.cp_selected_shiptos ON dbo.cp_order_detail.shipto_id = dbo.cp_selected_shiptos.shipto_id INNER JOIN
    dbo.cp_campaigns ON dbo.cp_elements.campaign_id = dbo.cp_campaigns.campaign_id
    GROUP BY dbo.cp_elements.campaign_id,, dbo.cp_elements.item_no,, dbo.cp_selected_shiptos.state,
    dbo.cp_orderables.qty_increment, dbo.cp_attribs.price, dbo.cp_orderables.est_qty
    ORDER BY dbo.cp_elements.item_no

    16,Project Z,10S,Ten Spot,10,1,0,9.9900
    16,Project Z,10S,Ten Spot,10,1,15,9.9900
    16,Project Z,10S,Ten Spot,10,1,0,9.9900

    That 2nd from last column is the qty_increment.. it's spitting out 3 rows when it should just show one, with 15 as the qty_increment.

    Thanks for any help/clues!

  2. #2
    Join Date
    Feb 2004

    I noticed that u have missing the dbo.cp_selected_shiptos.state column in SELECT COLUMNS. You have to give all the group by columns in SELECTed columns. Also Group by clause should have the ORDER BY COLUMN. This might be the reason that u face.

    U check it out and let me know. for Further,

  3. #3
    Join Date
    Jan 2004
    AHHHHH!! Duh! Stupid mistake... these are the usual kinds of mistakes I run into Nothing complicated or serious, just something really minor.

    Thanks for pointing that out I removed state from the group by and it works fine now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts