Solved

Sum rows in detail table of master/detail relationship

Posted on 2006-11-04
10
451 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now