Re: Show zero totals - do I need a crosstab query?

drinian wrote:
>> Can you export the table schema with a bit of sample data and I'll
>> load it up and take a look for you.
>
> Sorry for the delay. Here you go. As you can see no "Spacely
> Sprockets" show up. I'd like them to show zero value since there are
> no invoices of them. Thanks for any input:

Sorry for my delay in looking at this but I've been extremely busy.

Try this:

SELECT
Sum(ifnull(Invoices.TotalCharge,0)) AS Amount,
SaleItem.SaleItem,
SaleItem.SaleItemID
FROM `saleitem`
LEFT JOIN `invoiceitem` USING(`SaleItemID`)
LEFT JOIN `invoices` ON `invoiceitem`.`InvoiceNumber` =
`invoices`.`InvoiceNumber`
LEFT JOIN `invoicestatus` ON `invoices`.`InvoiceStatusID` =
`invoicestatus`.`InvoiceStatusID`
WHERE `InvoiceStatus`.`InvoiceStatus` != 'Void' AND
MONTH(`invoices`.`ServiceDate`) = '02'
AND YEAR(`invoices`.`ServiceDate`) = '2007'
OR `invoiceitem`.`SaleItemID` IS NULL
GROUP BY `SaleItem`.`SaleItem`
ORDER BY `SaleItem`.`SaleItem`