CODEWITHSUNDEEP
sqlsql-serveransi-sql
Underscore
Underscore

Reputation: 1057

Return Sum from another table in join with duplicates

I have the following 2 tables:

tblEventCustomers

EventCustomerId EventId CustomerId  InvoiceLineId
1002            100     5           21
1003            100     6           21
1004            100     7           22
1005            101     9           23

tblInvoiceLines

InvoiceLineId   Quantity    Price
21              2           25
22              1           12.5
23              1           34

I want to return the number of customers on an event and the total of the invoice lines for that event:

EventId     No. Delegates   Total
100         3               37.5
101         1               34

I have tried the following function:

CREATE FUNCTION dbo.udfInvoiceLineTotal
(
    @eventId AS INT
)
RETURNS MONEY
BEGIN
    DECLARE @returnAmount AS MONEY;
    SET @returnAmount = (
    select sum(Price) from tblInvoiceLines as IL
    where il.InvoiceLineId in
    (
        SELECT InvoiceLineId
        FROM tblEventCustomers
        where EventId = @eventId
    )   
    )
    RETURN @returnAmount;
END

And used it as below:

select      ed.EventId, 
            COUNT(1),
            dbo.udfInvoiceLineTotal(ed.EventId) from tblEventCustomers as ED

inner join  tblInvoiceLines as IL
on          IL.InvoiceLineId = ED.InvoiceLineId
group by    ed.EventId

This returns me the results I want, however I wanted to see if I was missing any way to do it in a more ANSI SQL way, i.e. using Sub-Queries rather than a database function?

The approach below returns me duplicates in the sum:

select  ed.EventId,
        SUM(il.Price),
        COUNT(1)
from    tblEventCustomers as ed
inner join tblInvoiceLines as il
on      ed.InvoiceLineId = il.InvoiceLineId
group by (ed.EventId)

Edit

Apologies for all those who answered, I realise I had a typo in my original data set which meant some of the suggested approaches worked for the dataset I presented at first but don't work for the full data set.

Upvotes: 0

Views: 1528

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You need to pre-aggregate the results before the join. You can use subqueries for this:

select c.EventId, c.NumCustomers, ci.price
from (select c.EventId, count(*) as NumCustomers
      from tblEventCustomers c
      group by c.EventId
     ) c left join
     (select c.EventId, sum(i.price) as price
      from (select distinct EventId, InvoiceLineId
            from tblEventCustomers c
           ) c join
           tblInvoiceLines i
           on i.InvoiceLineId = c.InvoiceLineId
      group by c.EventId
     ) ci
     on c.EventId = ci.EventId;

What makes this confusing is that you have to join the customers table to the invoice table to get the event id.

Upvotes: 0

SQLnbe
SQLnbe

Reputation: 868

You might want to look into CTEs (Common Table Expression) where you pre-calculate/group for one table then join the results with the other table based on a column and display the results all together. It's similar to sub-queries but I like CTE's more because they are easier to read.

see these links as a reference, hope this helps:

https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx

Upvotes: 0

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

Would this work for you? It gets the results you are looking for

SELECT c.EventId, SUM(Quantity), SUM(i.price)
FROM tblInvoiceLines i
   JOIN (SELECT DISTINCT 
         EventId, CustomerId, InvoiceLineId
        FROM tblEventCustomers) c ON i.InvoiceLineId = c.InvoiceLineId
GROUP BY c.EventId

Upvotes: 1

Andrew
Andrew

Reputation: 8758

If I understand you correctly, you'll need a subquery to get rid of what you're calling duplicates, which is being caused by the EventCustomerId column, which you're not including.

select
  ec.eventid,
  count (ec.CustomerId),
  sum(il.price)

from
  (select
     eventid,
     customerid,
     invoicelineid
   from
     tblEventCustomers
    group by
      eventid,
      customerid,
      invoicelineid)
   EC
inner join tblInvoiceLines IL
  on ec.invoicelineid = il.invoicelineid
group by
  ec.eventid

SQL Fiddle

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

If you modify your last approach like this, it should give you the desired results:

select  ed.EventId,
        SUM(il.Price/il.quantity)
from    tblEventCustomers as ed
inner join tblInvoiceLines as il
on      ed.InvoiceLineId = il.InvoiceLineId
group by (ed.EventId)
having COUNT(ed.EventCustomerId) > 5

And to include the number of delegates, just add SUM(il.quantity)

Upvotes: 1

Related Questions