Solved

Sum rows in detail table of master/detail relationship

Posted on 2006-11-04
10
460 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?
0
Comment
Question by:DanLockwood
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17874657
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
 

Author Comment

by:DanLockwood
ID: 17874746
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17874763
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17874774
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17874789
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
 

Author Comment

by:DanLockwood
ID: 17874793
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17874832
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
 
LVL 29

Accepted Solution

by:
Nightman earned 325 total points
ID: 17874838
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17874850
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17874854
Uncomment the INNER JOINS if they are necessary for filtering, otehrwise leave them commented.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question