Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sum rows in detail table of master/detail relationship

Posted on 2006-11-04
10
Medium Priority
?
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

715 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