We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Sum rows in detail table of master/detail relationship

DanLockwood
DanLockwood asked
on
Medium Priority
729 Views
Last Modified: 2008-01-09
I need to create a total invoice value from the detail table of my master/detail relationship.  I think it should go something like this but i'm getting some unexpected results:

SELECT master.id, sum(detail.quantity * detail.unitprice) * 1.0725 + master.laborprice

I've got the following tables setup:
CREATE TABLE [dbo].[tblInvoice_Master] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [fkEmployee] [int] NULL ,
      [fkCustomer] [int] NULL ,
      [fkContact] [int] NULL ,
      [fkPaymentTerms] [int] NULL ,
      [InvoiceDate] [datetime] NULL ,
      [Description] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PercentSalesTax] [decimal](7, 6) NULL ,
      [Shipping] [money] NULL ,
      [Revision] [int] NULL ,
      [Labor] [money] NULL ,
      [FabricatedLabor] [money] NULL ,
      [fkStatusCode] [int] NULL ,
      [Notes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [fkBillingAddress] [int] NULL ,
      [fkSalesOrder] [int] NULL ,
      [fkQuote] [int] NULL ,
      [CustomerPO] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [InvoiceNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [fkProject] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblInvoice_Detail] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [fkMaster] [int] NULL ,
      [Quantity] [decimal](16, 4) NULL ,
      [ManufacturerPartNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShortDescription] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Description] [varchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [UnitPrice] [money] NULL ,
      [Taxable] [bit] NULL
) ON [PRIMARY]

Any suggestions?
Comment
Watch Question

Commented:
What are the unexpected results?
Errors where master.Labor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause?
or an error in the caclulation?
or master.laborprice does not exist?

Author

Commented:
I should have been more specific; apologies.  Below is the query I am running.


SELECT DISTINCT
                      tblInvoice_Master.ID, tblInvoice_Master.InvoiceDate,
                      CASE tblinvoice_detail.taxable WHEN 1 THEN SUM(tblInvoice_Detail.Quantity * tblInvoice_Detail.UnitPrice) * (1 + tblinvoice_Master.percentsalestax)
                      WHEN 0 THEN SUM(tblInvoice_Detail.Quantity * tblInvoice_Detail.UnitPrice) END AS Total
FROM         tblInvoice_Master INNER JOIN
                      tblInvoice_Detail ON tblInvoice_Master.ID = tblInvoice_Detail.fkMaster INNER JOIN
                      tblStatusCode ON tblInvoice_Master.fkStatusCode = tblStatusCode.ID INNER JOIN
                      tblCustomer ON tblInvoice_Master.fkCustomer = tblCustomer.ID INNER JOIN
                      tblEmployee ON tblInvoice_Master.fkEmployee = tblEmployee.ID
GROUP BY tblInvoice_Master.ID, tblInvoice_Master.InvoiceDate, tblInvoice_Master.InvoiceNumber, tblStatusCode.Description, tblCustomer.CompanyName,
                      tblEmployee.UserID, tblInvoice_Master.Labor, tblInvoice_Master.Shipping, tblInvoice_Master.PercentSalesTax, tblInvoice_Detail.Taxable
ORDER BY tblInvoice_Master.ID DESC


The issue is that the query will return the same master record more than once in some cases.  For example if I know the total should be $500, the  query might return two instances of invoice #577; one with a total of $100 and the other with a total of $235.29.  I think it is related to using the SUM() function incorrectly.  Example results below; note #619 and 618:

ID      InvoiceDate      Total
621      11/2/2006      74.0025
621      11/2/2006      100000
620      11/2/2006      100
619      11/2/2006      277.134
619      11/2/2006      285
618      11/2/2006      10111.7445
618      11/2/2006      16748.41
617      11/2/2006      283.8
616      11/2/2006      167.505
            

Commented:
If you have multiple detail records with DIFFERENT .Taxable columns you should get multiple records. Can you confirm whether or not this is the case for the examples in question?
CERTIFIED EXPERT
Top Expert 2012

Commented:
First of all lose the DISTINCT, the GROUP BY makes it redundant.  But to answer your specific question one or more of the following columns is different that is why it is showing up twice:

tblInvoice_Master.ID, tblInvoice_Master.InvoiceDate, tblInvoice_Master.InvoiceNumber, tblStatusCode.Description, tblCustomer.CompanyName, tblEmployee.UserID, tblInvoice_Master.Labor, tblInvoice_Master.Shipping, tblInvoice_Master.PercentSalesTax, tblInvoice_Detail.Taxable

Temporarily Include them all in your Select list and it will be obvious which one it is / they are.  You will then have to decide what to do about it, the most obvious solution is to remove the column(s) from the GROUP BY clause.
CERTIFIED EXPERT
Top Expert 2012

Commented:
By the way and unrelated, you may find the following a tad more efficient.  
Instead of:
      CASE tblinvoice_detail.taxable
            WHEN 1 THEN SUM(tblInvoice_Detail.Quantity * tblInvoice_Detail.UnitPrice) * (1 + tblinvoice_Master.percentsalestax)
                WHEN 0 THEN SUM(tblInvoice_Detail.Quantity * tblInvoice_Detail.UnitPrice)
      END AS Total

This:
SUM(tblInvoice_Detail.Quantity * tblInvoice_Detail.UnitPrice) * (1 + tblinvoice_detail.taxable * tblinvoice_Master.percentsalestax) AS Total

Author

Commented:
It seems like the duplicate rows are for the tblInvoice_Detail.Taxable column.  One group for both taxable and not taxable.  If I take tblInvoice_Detail.Taxable from the GROUP BY clause then I get the following error:

Column 'tblInvoice_Detail.Taxable' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Right.  What is happening is that the fkMaster contains both taxable = 0 and taxable = 1, so you are going to ahve to resort to using a derived table to get your totals.
Commented:
anthony is right about the distinct - it is redundant. Give this a try (apologies - no access to QA right now, but this should work. Probably a more efficient way of doing it, but I am tired and on my way to bed - note the use of table aliases to make your life easier in future)

SELECT
 m.ID,
 m.InvoiceDate,
 SUM(t.total) + (SUM(d.total) * (1 + m.percentsalestax)) AS Total
FROM
  tblInvoice_Master m
  INNER JOIN (SELECT fkmaster,SUM(Quantity * UnitPrice)  as total FROM tblInvoice_Detail WHERE taxable=1 GROUP BY fkmaster,taxable) as d ON m.ID=d.fkMaster
  INNER JOIN (SELECT fkmaster,SUM(Quantity * UnitPrice)  as total FROM tblInvoice_Detail WHERE taxable=0 GROUP BY fkmaster,taxable) as t ON m.ID=t.fkMaster
  INNER JOIN tblStatusCode sc ON m.fkStatusCode = sc.ID
  INNER JOIN tblCustomer cc ON m.fkCustomer = cc.ID
  INNER JOIN tblEmployee ee ON m.fkEmployee = ee.ID
GROUP BY
  m.ID,
  m.InvoiceDate,
  m.InvoiceNumber,
  tblStatusCode.Description,
  tblCustomer.CompanyName,
  tblEmployee.UserID,
  m.Labor,
  m.Shipping,
  m.PercentSalesTax
ORDER BY
  m.ID DESC

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2012

Commented:
Actually on second thought this may work:

SELECT      tblInvoice_Master.ID,
      tblInvoice_Master.InvoiceDate,
      SUM(tblInvoice_Detail.Quantity * tblInvoice_Detail.UnitPrice * (1 + tblinvoice_detail.taxable * tblinvoice_Master.percentsalestax)) AS Total
FROM      tblInvoice_Master
      INNER JOIN tblInvoice_Detail ON tblInvoice_Master.ID = tblInvoice_Detail.fkMaster
--      INNER JOIN tblStatusCode ON tblInvoice_Master.fkStatusCode = tblStatusCode.ID
--      INNER JOIN tblCustomer ON tblInvoice_Master.fkCustomer = tblCustomer.ID
--      INNER JOIN tblEmployee ON tblInvoice_Master.fkEmployee = tblEmployee.ID
GROUP BY
      tblInvoice_Master.ID,
      tblInvoice_Master.InvoiceDate
ORDER BY
      tblInvoice_Master.ID DESC
CERTIFIED EXPERT
Top Expert 2012

Commented:
Uncomment the INNER JOINS if they are necessary for filtering, otehrwise leave them commented.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.