SUM query problem

[ryan010101]

The below query is not working. I have a table of order contents. Each record in the table is a line item. I need to add up the quantities for each order and display each order whose total quantity is >= 100. Any suggestions?

SELECT itm_ord_num, Sum( qty ) AS Qty1, oper_id3, vfy_date
FROM carton_contents
WHERE vfy_date >= '2004-12-06'
AND vfy_date <= '2004-12-06'
AND Qty1 >=100
GROUP BY itm_ord_num
ORDER BY Qty1 DESC
LIMIT 0 , 30

thanks
Ryan

 

[cLFlaVA]

how about

  SELECT itm_ord_num
       , SUM( qty ) as Qty1
       , oper_id3
       , vfy_date
    FROM carton_contents
   WHERE vfy_date = '2004-12-06'
     AND SUM( qty ) >= 100
GROUP BY itm_ord_num
ORDER BY SUM( qty ) DESC
   LIMIT 30

*cLFlaVA
----------------------------
[tt]insert funny quotation here.[/tt]

 

[ryan010101]

Thanks for the quick response. When I try that I'm getting "Invalid use of group function" (I just plugged the query into phpMyAdmin).

 

[r937]

select itm_ord_num
     , sum(qty) as qty1
     , oper_id3
     , vfy_date
  from carton_contents
 where vfy_date = '2004-12-06'
group 
    by itm_ord_num
     , oper_id3
     , vfy_date
having sum(qty) >= 100
order 
    by sum(qty) desc
limit 30

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ryan010101]

Thanks but I'm still getting "Invalid use of group function". ??

 

[ryan010101]

If I take out the ORDER BY part, it works. thanks

 

[ryan010101]

The problem was the ORDER BY part. Must not be able to use SUM in that part. This works for me:

SELECT itm_ord_num, Sum( qty ) AS Qty1, oper_id3, vfy_date
FROM carton_contents
WHERE vfy_date >= '2004-12-06'
AND vfy_date <= '2004-12-06'
GROUP BY itm_ord_num, oper_id3, vfy_date
ORDER BY Qty1 DESC

Thanks for all your help!