drinian wrote:
> On May 8, 10:07 am, lark <ham...@sbcdeglobalspam.net> wrote:
>> drinian wrote:
>>> I have three tables: Invoices, InvoiceStatus, SaleItem
>>> I want to run a query that totals the invoices by the sale item.
>>> Here's my query:
>>> SELECT Sum(Invoices.TotalCharge) AS Amount, SaleItem.SaleItem,
>>> SaleItem.SaleItemID FROM SaleItem RIGHT JOIN ((Invoices LEFT JOIN
>>> InvoiceStatus ON Invoices.InvoiceStatusID =
>>> InvoiceStatus.InvoiceStatusID) LEFT JOIN InvoiceItem ON
>>> Invoices.InvoiceNumber = InvoiceItem.InvoiceNumber) ON
>>> SaleItem.SaleItemID = InvoiceItem.SaleItemID WHERE
>>> InvoiceStatus.InvoiceStatus != 'Void' AND MONTH(ServiceDate) = '02'
>>> AND YEAR(ServiceDate) = '2007' GROUP BY SaleItem.SaleItem ORDER BY
>>> SaleItem.SaleItem
>>> This works fine, but I would like the query to show a zero value for
>>> the sale items not purchased. Currently this query will only show the
>>> totals of actual sale items that were sold. Is there a way to force
>>> the query to always show all sales items, even the ones that total to
>>> zero (no sale item sold that month)?
>>> Also, is this what a crosstab query is?
>>> I appreciate any advice or input.
>> looks like you're using a fourth table called invoiceitem. would you be
>> able to post the output of these commands:
>> desc Invoices;
>> desc InvoiceStatus;
>> desc SaleItem;
>> desc InvoiceItem;
>>
>> --
>> lark -- ham...@sbcdeglobalspam.net
>> To reply to me directly, delete "despam".
>
> Opps, that's right. I forgot about InvoiceItems. Here's the
> descriptions:
>
> mysql> desc Invoices;
> +------------------+---------------+------+-----+---------
> +----------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +------------------+---------------+------+-----+---------
> +----------------+
> | InvoiceNumber | bigint(20) | | PRI | NULL |
> auto_increment |
> | JobID | bigint(20) | YES | MUL | 0
> | |
> | AssignedDate | datetime | YES | | NULL
> | |
> | ServiceDate | datetime | YES | | NULL
> | |
> | BillName | varchar(50) | YES | | NULL
> | |
> | BillAddress | varchar(50) | YES | | NULL
> | |
> | BillCityID | bigint(20) | YES | MUL | 0
> | |
> | BillState | varchar(50) | YES | | NULL
> | |
> | BillZip | varchar(50) | YES | | NULL
> | |
> | ServiceName | varchar(50) | YES | | NULL
> | |
> | ServiceAddress | varchar(50) | YES | | NULL
> | |
> | ServiceCityID | bigint(20) | YES | MUL | 0
> | |
> | ServiceState | varchar(50) | YES | | NULL
> | |
> | ServiceZip | varchar(50) | YES | | NULL
> | |
> | InvoiceStatusID | bigint(20) | YES | MUL | 0
> | |
> | Route | bigint(20) | YES | | 0
> | |
> | TermsID | bigint(20) | YES | MUL | 0
> | |
> | Subtotal | decimal(20,4) | YES | | 0.0000
> | |
> | SalesTax | decimal(20,4) | YES | | 0.0000
> | |
> | TotalCharge | decimal(20,4) | YES | | 0.0000
> | |
> | Taxable | tinyint(4) | YES | | NULL
> | |
> | TaxItemID | bigint(20) | YES | MUL | 0
> | |
> | Notes | text | YES | | NULL
> | |
> | Recorded | tinyint(4) | YES | | 0
> | |
> | RecordedDate | datetime | YES | | NULL
> | |
> | RecordedIncomeID | bigint(20) | YES | | NULL
> | |
> +------------------+---------------+------+-----+---------
> +----------------+
> 26 rows in set (0.00 sec)
>
> mysql> desc InvoiceStatus;
> +-----------------+-------------+------+-----+---------
> +----------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +-----------------+-------------+------+-----+---------
> +----------------+
> | InvoiceStatusID | bigint(20) | | PRI | NULL |
> auto_increment |
> | InvoiceStatus | varchar(50) | YES | | NULL
> | |
> +-----------------+-------------+------+-----+---------
> +----------------+
> 2 rows in set (0.00 sec)
>
> mysql> desc SaleItem;
> +---------------------+-------------+------+-----+---------
> +----------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +---------------------+-------------+------+-----+---------
> +----------------+
> | SaleItemID | bigint(20) | | PRI | NULL |
> auto_increment |
> | SaleItem | varchar(50) | YES | | NULL
> | |
> | SaleItemDescription | varchar(50) | YES | | NULL
> | |
> | Taxable | tinyint(4) | YES | | 1
> | |
> +---------------------+-------------+------+-----+---------
> +----------------+
> 4 rows in set (0.00 sec)
>
> mysql> desc InvoiceItem;
> +-----------------+---------------+------+-----+---------
> +----------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +-----------------+---------------+------+-----+---------
> +----------------+
> | InvoiceItemID | bigint(20) | | PRI | NULL |
> auto_increment |
> | InvoiceNumber | bigint(20) | YES | MUL | 0
> | |
> | SaleItemID | bigint(20) | YES | MUL | 0
> | |
> | ItemDescription | varchar(50) | YES | | NULL
> | |
> | Taxable | tinyint(4) | YES | | 1
> | |
> | Charge | decimal(20,4) | YES | | 0.0000
> | |
> +-----------------+---------------+------+-----+---------
> +----------------+
> 6 rows in set (0.00 sec)
>
> Thank you for the help.
>
have you tried it this way:

SELECT
Sum(Invoices.TotalCharge) AS Amount,
SaleItem.SaleItem,
SaleItem.SaleItemID
FROM
Invoices Join InvoiceStatus on Invoices.InvoiceStatusId =
InvoiceStatus.InvoiceStatusId
Join InvoiceItem on Invoice.InvoiceNumber = InvoiceItem.InvoiceNumber
Right Join SaleItem on InvoiceItem.SaleItemID = SaleItemID

WHERE
InvoiceStatus.InvoiceStatus != 'Void' AND
MONTH(ServiceDate) = '02' AND
YEAR(ServiceDate) = '2007'

GROUP BY SaleItem.SaleItem
ORDER BY SaleItem.SaleItem

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".