Professional Web Applications Themes

Show zero totals - do I need a crosstab query? - MySQL

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

  1. #1

    Default Show zero totals - do I need a crosstab query?

    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 Guest

  2. #2

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

    drinian wrote: 

    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 -- net
    To reply to me directly, delete "despam".
    lark Guest

  3. #3

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

    On May 8, 10:07 am, lark <net> wrote: 





    >
    > 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 -- net
    > To reply to me directly, delete "despam".[/ref]

    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 Guest

  4. #4

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

    drinian wrote: 
    >> 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 -- net
    >> To reply to me directly, delete "despam".[/ref]
    >
    > 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.
    >[/ref]
    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 -- net
    To reply to me directly, delete "despam".
    lark Guest

  5. #5

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

    On May 8, 10:52 am, lark <net> wrote: [/ref]
    > [/ref]






    >
    > 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 -- net
    > To reply to me directly, delete "despam".[/ref]

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

    Any other thoughts?

    Thanks.

    drinian Guest

  6. #6

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

    On 8 May 2007 06:16:08 -0700, drinian <com> wrote:
     

    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) ?
    subtenante Guest

  7. #7

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

    On 9 May, 06:28, subtenante <com> wrote: 



    >
    > 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 -[/ref]

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

    Captain Guest

  8. #8

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

    On May 9, 1:28 am, subtenante <com> wrote: 



    >
    > 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) ?[/ref]

    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.

    drinian Guest

  9. #9

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

    On 9 May, 13:42, drinian <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > 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 -[/ref]

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

    Captain Guest

  10. #10

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

    > Can you export the table schema with a bit of sample data and I'll 

    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 CHT=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 CHT=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 CHT=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 CHT=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);


    drinian Guest

  11. #11

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

    drinian wrote: 
    >
    > 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:[/ref]

    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`


    Paul Guest

Similar Threads

  1. Crosstab Query Export
    By PrinBD in forum Coldfusion Database Access
    Replies: 1
    Last Post: January 17th, 05:41 PM
  2. Crosstab Query with Date Fields
    By mostlySimple in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 5th, 04:28 AM
  3. Server 2000 Crosstab Query
    By Alastair in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 4th, 02:19 PM
  4. Crosstab query - Nearly there!
    By Vishal Parkar in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 09:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139