Sum rows in detail table of master/detail relationship

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?
DanLockwoodAsked:
Who is Participating?
 
NightmanCTOCommented:
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
0
 
NightmanCTOCommented:
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?
0
 
DanLockwoodAuthor 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
            
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
NightmanCTOCommented:
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?
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
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
0
 
DanLockwoodAuthor 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.
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
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
0
 
Anthony PerkinsCommented:
Uncomment the INNER JOINS if they are necessary for filtering, otehrwise leave them commented.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.