Query Help

[zinja]

I have a table that has sales data in it. Sometimes there are no sales for a particular department, but I would still like that row returned in the query (just with a zero count and zero total). Any ideas?

Here is the query I currently use:

SELECT transdepartment AS DEPT, SUM(amount * quantity) " .
						"AS TOTAL, COUNT(DISTINCT(transactionid)) AS COUNT FROM " .
						"tblTransactions t, tblPayments p WHERE t.transdepartment " .
						"<> 'ROA' AND t.reportid= " . $reportid . " AND " .
						"t.transactionnum = p.transactionnum AND p.paymentmethod " .
						"<> 'Charge' AND t.storeid = '" . $storeid . "' AND " .
						"t.registernum = '" . $registernum . "' GROUP BY " .
						"transdepartment ORDER BY transdepartment ASC

Thanks!

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[r937]

this can only be done if you have a department table

SELECT [red]d.deptid[/red]      AS DEPT
     , SUM(amount * quantity) AS TOTAL
     , COUNT(DISTINCT(transactionid)) AS COUNT 
  FROM [red]departments d[/red]
LEFT OUTER
  JOIN tblTransactions t
    ON t.transdepartment = [red]d.deptid[/red]
   AND t.reportid = $reportid 
   AND t.storeid = '$storeid' 
   AND t.registernum = '$registernum' 
LEFT OUTER
  JOIN tblPayments p 
    ON p.transactionnum = t.transactionnum
   AND p.paymentmethod <> 'Charge' 
 WHERE [red]d.deptid[/red] <> 'ROA' 
GROUP 
    BY [red]d.deptid[/red]
ORDER 
    BY [red]d.deptid[/red] ASC

r937.com | rudy.ca