SEO

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.

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 -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

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.

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".

On May 8, 10:52 am, lark <ham...@sbcdeglobalspam.net> wrote:
> 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 -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".

Unfortunately, that query returns equal amounts for each sale item.

Any other thoughts?

Thanks.

On 8 May 2007 06:16:08 -0700, drinian <AdamKrell@gmail.com> 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)?

Maybe i got something wrong, but it seems to me you are using a RIGHT
JOIN just after your FROM SaleItem. If you want to be sure to have all
the SaleItems represented at least once, should it not be a LEFT JOIN
(in which case you might get a NULL instead of 0 in your Amount
column) ?

On 9 May, 06:28, subtenante <zzsubtenant...@gmail.com> wrote:
> On 8 May 2007 06:16:08 -0700, drinian <AdamKr...@gmail.com> 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)?
>
> Maybe i got something wrong, but it seems to me you are using a RIGHT
> JOIN just after your FROM SaleItem. If you want to be sure to have all
> the SaleItems represented at least once, should it not be a LEFT JOIN
> (in which case you might get a NULL instead of 0 in your Amount
> column) ?- Hide quoted text -
>
> - Show quoted text -

But a NULL instead of 0 can be solved with an IFNULL(`column`,0)

On May 9, 1:28 am, subtenante <zzsubtenant...@gmail.com> wrote:
> On 8 May 2007 06:16:08 -0700, drinian <AdamKr...@gmail.com> 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)?
>
> Maybe i got something wrong, but it seems to me you are using a RIGHT
> JOIN just after your FROM SaleItem. If you want to be sure to have all
> the SaleItems represented at least once, should it not be a LEFT JOIN
> (in which case you might get a NULL instead of 0 in your Amount
> column) ?

No, changing it to a LEFT JOIN returns equal amounts for each sale
item (like lark's suggestion). Changing lark's statement to be a LEFT
JOIN returns the correct amounts but will not show zero totals for
sale items with no invoices.

It's got me stumped.

On 9 May, 13:42, drinian <AdamKr...@gmail.com> wrote:
> On May 9, 1:28 am, subtenante <zzsubtenant...@gmail.com> wrote:
>
>
>
>
>
> > On 8 May 2007 06:16:08 -0700, drinian <AdamKr...@gmail.com> 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)?
>
> > Maybe i got something wrong, but it seems to me you are using a RIGHT
> > JOIN just after your FROM SaleItem. If you want to be sure to have all
> > the SaleItems represented at least once, should it not be a LEFT JOIN
> > (in which case you might get a NULL instead of 0 in your Amount
> > column) ?
>
> No, changing it to a LEFT JOIN returns equal amounts for each sale
> item (like lark's suggestion). Changing lark's statement to be a LEFT
> JOIN returns the correct amounts but will not show zero totals for
> sale items with no invoices.
>
> It's got me stumped.- Hide quoted text -
>
> - Show quoted text -

Can you export the table schema with a bit of sample data and I'll
load it up and take a look for you.

> 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:

--
-- Table structure for table `InvoiceItem`
--

CREATE TABLE `InvoiceItem` (
`InvoiceItemID` bigint(20) NOT NULL auto_increment,
`InvoiceNumber` bigint(20) default '0',
`SaleItemID` bigint(20) default '0',
`ItemDescription` varchar(50) default NULL,
`Taxable` tinyint(4) default '1',
`Charge` decimal(20,4) default '0.0000',
PRIMARY KEY (`InvoiceItemID`),
KEY `ItemID` (`SaleItemID`),
KEY `InvoiceNumber` (`InvoiceNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=42451 ;

--
-- Dumping data for table `InvoiceItem`
--

INSERT INTO `InvoiceItem` (`InvoiceItemID`, `InvoiceNumber`,
`SaleItemID`, `ItemDescription`, `Taxable`, `Charge`) VALUES
(1, 130461, 1, '', 1, 13.0800),
(2, 130465, 1, '', 1, 23.3600),
(3, 130466, 1, '', 1, 11.2100),
(4, 130467, 3, '', 1, 33.6400),
(5, 130468, 1, '', 1, 20.5600),
(6, 130469, 3, '', 1, 15.8900),
(7, 130470, 3, '', 1, 14.0200),
(8, 130471, 3, '', 1, 401.8700);

-- --------------------------------------------------------

--
-- Table structure for table `InvoiceStatus`
--

CREATE TABLE `InvoiceStatus` (
`InvoiceStatusID` bigint(20) NOT NULL auto_increment,
`InvoiceStatus` varchar(50) default NULL,
PRIMARY KEY (`InvoiceStatusID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `InvoiceStatus`
--

INSERT INTO `InvoiceStatus` (`InvoiceStatusID`, `InvoiceStatus`)
VALUES
(1, 'Open'),
(2, 'Paid'),
(3, 'Void'),
(4, 'Bad Debt');

-- --------------------------------------------------------

--
-- Table structure for table `Invoices`
--

CREATE TABLE `Invoices` (
`InvoiceNumber` bigint(20) NOT NULL auto_increment,
`JobID` bigint(20) default '0',
`AssignedDate` datetime default NULL,
`ServiceDate` datetime default NULL,
`BillName` varchar(50) default NULL,
`BillAddress` varchar(50) default NULL,
`BillCityID` bigint(20) default '0',
`BillState` varchar(50) default NULL,
`BillZip` varchar(50) default NULL,
`ServiceName` varchar(50) default NULL,
`ServiceAddress` varchar(50) default NULL,
`ServiceCityID` bigint(20) default '0',
`ServiceState` varchar(50) default NULL,
`ServiceZip` varchar(50) default NULL,
`InvoiceStatusID` bigint(20) default '0',
`Route` bigint(20) default '0',
`TermsID` bigint(20) default '0',
`Subtotal` decimal(20,4) default '0.0000',
`SalesTax` decimal(20,4) default '0.0000',
`TotalCharge` decimal(20,4) default '0.0000',
`Taxable` tinyint(4) default NULL,
`TaxItemID` bigint(20) default '0',
`Notes` text,
`Recorded` tinyint(4) default '0',
`RecordedDate` datetime default NULL,
`RecordedIncomeID` bigint(20) default NULL,
PRIMARY KEY (`InvoiceNumber`),
KEY `BillCityID` (`BillCityID`),
KEY `JobID` (`JobID`),
KEY `ServiceCityID` (`ServiceCityID`),
KEY `ServiceCode` (`InvoiceStatusID`),
KEY `TaxItemID` (`TaxItemID`),
KEY `TermsID` (`TermsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=144809 ;

--
-- Dumping data for table `Invoices`
--

INSERT INTO `Invoices` (`InvoiceNumber`, `JobID`, `AssignedDate`,
`ServiceDate`, `BillName`, `BillAddress`, `BillCityID`, `BillState`,
`BillZip`, `ServiceName`, `ServiceAddress`, `ServiceCityID`,
`ServiceState`, `ServiceZip`, `InvoiceStatusID`, `Route`, `TermsID`,
`Subtotal`, `SalesTax`, `TotalCharge`, `Taxable`, `TaxItemID`,
`Notes`, `Recorded`, `RecordedDate`, `RecordedIncomeID`) VALUES
(130461, 648, '2007-02-08 00:00:00', '2007-02-08 00:00:00', 'Mike',
'', 68, 'NJ', '', 'Mike', '', 68, 'NJ', '', 2, 3, 1, 13.0800, 0.9200,
14.0000, 1, 1, '', 1, '2007-02-09 10:39:01', 529),
(130465, 4689, '2007-02-19 00:00:00', '2007-02-19 00:00:00',
'Marlton', '', 14, 'NJ', '', 'Marlton', '', 14, 'NJ', '08053', 2, 1,
1, 23.3600, 1.6400, 25.0000, 1, 1, '', 1, '2007-02-20 10:20:40', 536),
(130466, 3844, '2007-02-19 00:00:00', '2007-02-19 00:00:00',
'Marlton2', '', 14, 'NJ', '', 'Marlton2', '', 14, 'NJ', '', 2, 1, 1,
11.2100, 0.7800, 11.9900, 1, 1, '', 1, '2007-02-20 10:20:40', 536),
(130467, 5179, '2007-02-23 00:00:00', '2007-02-23 00:00:00', 'Salon',
'', 12, 'NJ', '', 'Salon', '', 12, 'NJ', '', 2, 1, 1, 33.6400, 2.3500,
35.9900, 1, 1, '', 1, '2007-02-26 09:55:51', 540),
(130468, 3288, '2007-02-16 00:00:00', '2007-02-16 00:00:00', 'Bas',
'', 5, 'NJ', '', 'Bas', '', 5, 'NJ', '', 2, 2, 1, 20.5600, 1.4400,
22.0000, 1, 1, '', 1, '2007-02-17 10:57:43', 579),
(130469, 1309, '2007-02-16 00:00:00', '2007-02-16 00:00:00', 'Navy',
'', 5, 'NJ', '', 'Navy', '', 5, 'NJ', '', 2, 2, 1, 15.8900, 1.1100,
17.0000, 1, 1, '', 1, '2007-04-17 10:57:43', 579),
(130470, 5337, '2007-02-12 00:00:00', '2007-02-12 00:00:00',
'Hoagies', '', 48, 'NJ', '', 'Hoagies', '', 48, 'NJ', '', 2, 2, 1,
14.0200, 0.9800, 15.0000, 1, 1, '', 1, '2007-04-13 10:46:56', 576),
(130471, 2460, '2007-02-17 00:00:00', '2007-02-17 00:00:00',
'National', '', 24, 'NJ', '', 'National', '', 24, 'NJ', '', 1, 1, 2,
401.8700, 28.1300, 430.0000, 1, 1, '', 1, '2007-02-18 10:19:09', 580);

-- --------------------------------------------------------

--
-- Table structure for table `SaleItem`
--

CREATE TABLE `SaleItem` (
`SaleItemID` bigint(20) NOT NULL auto_increment,
`SaleItem` varchar(50) default NULL,
`SaleItemDescription` varchar(50) default NULL,
`Taxable` tinyint(4) default '1',
PRIMARY KEY (`SaleItemID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `SaleItem`
--

INSERT INTO `SaleItem` (`SaleItemID`, `SaleItem`,
`SaleItemDescription`, `Taxable`) VALUES
(1, 'CG', 'Coswell Cogs', 1),
(2, 'SS', 'Spacely Sprockets', 1),
(3, 'MC', 'Misc.', 1);