Need help with COUNT with variables

jaustill

Here is my MSSQL table:

ORDER NUMBER | ITEM NUMBER | QUANTITY | PRICE |

67895 | BR124 | 4 | 25.00
67895 | FG536 | 1 | 2.00
67895 | M56 | 1 | 2.00
67895 | PFHR3 | 1 | 3.00
99678 | XC124 | 1 | 4.00
99678 | SF36 | 1 | 40.00
99678 | VM56 | 1 | 4.00
99678 | YYR3 | 1 | 5.00

This works to show the COUNT of all orders today:
$result=mssql_query("SELECT COUNT(OEORDLIN_SQL.tot_qty_ordered*OEORDLIN_SQL.unit_price) as ordcount FROM OEORDHDR_SQL INNER JOIN OEORDLIN_SQL ON OEORDHDR_SQL.Ord_No = OEORDLIN_SQL.ord_no WHERE OEORDHDR_SQL.Entered_dt=20031013");
$ordcount = mssql_result($result,0,"ordcount");

But I only want the result to be a COUNT of orders where
the "Total" of the order is under 100. I tried to add the following into the query and just get errors
SUM(OEORDLIN_SQL.tot_qty_ordered*OEORDLIN_SQL.unit_price)<100

Can someone tell me how to put that in my query?

eoghain

SELECT COUNT(OEORDLIN_SQL.tot_qty_ordered*OEORDLIN_SQL.unit_price) as ordcount 
  FROM OEORDHDR_SQL 
 INNER JOIN OEORDLIN_SQL ON OEORDHDR_SQL.Ord_No = OEORDLIN_SQL.ord_no 
 WHERE OEORDHDR_SQL.Entered_dt=20031013
   AND SUM(OEORDLIN_SQL.tot_qty_ordered*OEORDLIN_SQL.unit_price)<100;

Lars_Berg

You can't use aggregates in a where clause

SELECT COUNT(*) as ordcount 
  FROM OEORDHDR_SQL 
 INNER JOIN OEORDLIN_SQL 
ON OEORDHDR_SQL.Ord_No = OEORDLIN_SQL.ord_no 
 WHERE OEORDHDR_SQL.Entered_dt=20031013
   having 
SUM(OEORDLIN_SQL.tot_qty_ordered*OEORDLIN_SQL.unit_price)<100

eoghain

Oops completely forgot. I always do that and then have to re-write it once it doesn't work. :eek: