# SQL Help: SUM function

**URL:** <https://boards.straightdope.com/t/sql-help-sum-function/654780>\
**Category:** Factual Questions\
**Created:** [April 3, 2013, 10:58pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780 "2013-04-03T22:58:14Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\
**Post date:** [April 3, 2013, 10:58pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/1 "2013-04-03T22:58:14Z")

</div>

I’m having some SQL trouble, and I’m hoping someone here can help.

I have three tables: one which lists information about products, one which lists information about stores selling those products and when, and one which lists information about how many customers have viewed a product (it has two columns, a date and the number of views on that date).

What I want to do is list, for a given date range, for each product, the number of stores selling that product and the number of customers who viewed that product.

I have a query like this:

```auto

SELECT [Products].[ProductID], 
            SUM([DailyViews].[Views]) as Views, 
           count(DISTINCT([ProducttoStoreLinkDetails].StoreID)) as Links 

FROM [Products], [DailyViews], [ProducttoStoreLinkDetails] 

WHERE 
            [Products].ProductID = [DailyViews].ProductID 
     and [ProducttoStoreLinkDetails].ProductKey =[Products].ProductKey 
  
     and [DailyViews].ViewDate > '2012-12-31' and [DailyViews].ViewDate < '2013-04-01' 

      and [ProducttoStoreLinkDetails].DateCreated > '2012-12-31' and [ProducttoStoreLinkDetails].DateCreated < '2013-04-01' 

group by [Products].ProductID 
order by Links desc

```

Here’s the problem: the count of Links comes out fine. The sum of views comes out as the sum of views \* the number of days in the range (90). If I use SUM(Distinct([DailyViews].[Views])) as Views, I get the correct sum – except that, for any days which had the same number of views, that value is only counted once (which is what distinct does, I know).

Where am I going wrong? How do I get the sum of the views value for each day, without it being multiplied times the number of days?

---

<div class="post-metadata">

**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\
**Post date:** [April 3, 2013, 11:39pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/2 "2013-04-03T23:39:31Z")

</div>

I suspect you’re missing a condition in your table join somewhere, which is causing it to evaluate the join as a Cartesian product. Without looking at the structure of the tables in detail, I think you need to add

```auto

and [DailyViews].ViewDate = [ProducttoStoreLinkDetails].DateCreated

```

to your WHERE clause.

---

<div class="post-metadata">

**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\
**Post date:** [April 3, 2013, 11:51pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/3 "2013-04-03T23:51:03Z")

</div>

Interesting. I had thought one join for each table was all that was needed, but that makes sense. I’ll try that and report back. Thanks!

---

<div class="post-metadata">

**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\
**Post date:** [April 4, 2013, 12:26am UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/4 "2013-04-04T00:26:01Z")

</div>

Adding that, the query executes successfully and I get 0 results.

It turns out that ViewDate is in date format while DateCreated is in datetime format. I’m now looking up conversion methods…

---

<div class="post-metadata">

**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\
**Post date:** [April 4, 2013, 12:34am UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/5 "2013-04-04T00:34:11Z")

</div>

Remember that when you join, you are basically creating all combinations of rows from all 3 tables and whittling that list down to what you want with the join/where clauses.

But, unless I am misinterpreting, you are joining 2 independent/marginally related sets of data together when you shouldn’t be (views and store links).

Alternatives:  
Subqueries in select:  
Select  
productid  
,(select sum(views) from … where …) as num\_views  
,(select count(stores) from … where …) as num\_stores  
from products  
where …  
Subqueries combined with union all and final sum:  
select  
product\_id  
,sum(num\_views) as num\_views  
,sum(num\_stores) as num\_stores  
from (  
select  
productid  
,sum(views) as num\_views  
,sum(0) as num\_stores  
from products  
inner join views  
where …  
UNION ALL  
select  
productid  
,sum(0) as num\_views  
,count as num\_stores  
from products  
inner join stores  
where …  
) t1  
group by  
productid

---

<div class="post-metadata">

**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\
**Post date:** [April 4, 2013, 1:03am UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/6 "2013-04-04T01:03:06Z")

</div>

> [@RaftPeople](#):
>
> But, unless I am misinterpreting, you are joining 2 independent/marginally related sets of data together when you shouldn’t be (views and store links).

You’re not misinterpreting; I munged the backstory slightly. In real life, these are both independent actions happening to the product, so it makes sense to report on the product and the two actions.

> [@](#):
>
> Alternatives:  
> Subqueries in select:  
> Select  
> productid  
> ,(select sum(views) from … where …) as num\_views  
> ,(select count(stores) from … where …) as num\_stores  
> from products  
> where …

I tried a variant of this, with a single join between tables 2 and 3 and subquery in the where, but I’ll try this – and your other suggestion – if I can’t get the datetime conversion to work (or if that method, itself, doesn’t work).

---

<div class="post-metadata">

**Author:** ![Bremidon](https://avatars.discourse-cdn.com/v4/letter/b/ac91a4/32.png) [@Bremidon](https://boards.straightdope.com/u/Bremidon)\
**Post date:** [April 4, 2013, 10:01pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/7 "2013-04-04T22:01:55Z")

</div>

The “subquery in select” idea would get my vote as probably being the easiest to implement correctly.

Your original sql has another common, somewhat hidden problem. Because your where conditions are really just inner joins, you could miss out on products that have views but no links, or links but no views. Simply joining up the other two tables by date would not remove this problem; it would merely cause the problem on each individual date. The subquery solution should avoid this problem, although you still need to be careful with your where conditions in the subqueries.

If you had many more products than views or links, then the “subquery with union” might be faster, but I don’t expect that this is the case with your system. In any event, you’d have to analyse the execution plan of both ideas to see which seems to work more efficiently with your system.

---

<div class="post-metadata">

**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\
**Post date:** [April 4, 2013, 10:54pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/8 "2013-04-04T22:54:35Z")

</div>

Sorry, folks, I just didn’t have time to work on this today, so no progress to report. I’m hoping to take some time tonight.

> [@Bremidon](#):
>
> The “subquery in select” idea would get my vote as probably being the easiest to implement correctly.
> 
> Your original sql has another common, somewhat hidden problem. Because your where conditions are really just inner joins, you could miss out on products that have views but no links, or links but no views. Simply joining up the other two tables by date would not remove this problem; it would merely cause the problem on each individual date. The subquery solution should avoid this problem, although you still need to be careful with your where conditions in the subqueries.

This is a really interesting point, and one I hadn’t considered – thank you! For the reasons you suggest, which are very real dangers, I will make that change.

> [@](#):
>
> If you had many more products than views or links, then the “subquery with union” might be faster, but I don’t expect that this is the case with your system. In any event, you’d have to analyse the execution plan of both ideas to see which seems to work more efficiently with your system.

There are about 30,000 products … so this actually is a real danger. I’ll only be running the query once (well, once successfully, umpteen times to get wrong results!), so I’m not too concerned, but I appreciate the caution. If/when this goes into production, I’ll let my DBAs worry about this angle. 🙂

---

<div class="post-metadata">

**Author:** ![hoHoHarmonium](https://avatars.discourse-cdn.com/v4/letter/h/e495f1/32.png) [@hoHoHarmonium](https://boards.straightdope.com/u/hoHoHarmonium)\
**Post date:** [April 4, 2013, 11:35pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/9 "2013-04-04T23:35:10Z")

</div>

in mssql:

and datediff(d,[DailyViews].ViewDate , [ProducttoStoreLinkDetails].DateCreated)=0

it’s the difference in days between the two dates.

all rdbms’s will have their own date methods.

---

<div class="post-metadata">

**Author:** ![AnalogSignal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/analogsignal/32/1085_2.png) [@AnalogSignal](https://boards.straightdope.com/u/AnalogSignal)\
**Post date:** [April 4, 2013, 11:48pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/10 "2013-04-04T23:48:57Z")

</div>

I think this should work (ignoring issues in date conversion). For future reference, it would be very helpful if you specify database platform and version. SQL syntax can vary widely.

```php
SELECT 
	q.ProductID, q.Views, SUM(l.StoreID) AS Links
FROM
(
	SELECT p.ProductID, p.ProductKey, SUM(dv.Views) as Views
	FROM
	[Products] p
		LEFT OUTER JOIN
	[DailyViews] dv
			ON dv.ProductID = p.ProductID
	WHERE dv.ViewDate > '2012-12-31' and dv.ViewDate < '2013-04-01' 
	GROUP BY
		p.ProductID, p.ProductKey
) q
	LEFT OUTER JOIN
[ProducttoStoreLinkDetails] l
		ON l.ProductKey = q.ProductKey
WHERE l.DateCreated > '2012-12-31' and l.DateCreated < '2013-04-01' 
GROUP BY 
	q.ProductID, q.Views

```

---

<div class="post-metadata">

**Author:** ![AnalogSignal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/analogsignal/32/1085_2.png) [@AnalogSignal](https://boards.straightdope.com/u/AnalogSignal)\
**Post date:** [April 5, 2013, 12:38am UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/11 "2013-04-05T00:38:30Z")

</div>

Oops, the first line should have a COUNT not a SUM:

```php

SELECT 
	q.ProductID, q.Views, COUNT(l.StoreID) AS Links
FROM
(
	SELECT p.ProductID, p.ProductKey, SUM(dv.Views) as Views
	FROM
	[Products] p
		LEFT OUTER JOIN
	[DailyViews] dv
			ON dv.ProductID = p.ProductID
	WHERE dv.ViewDate > '2012-12-31' and dv.ViewDate < '2013-04-01' 
	GROUP BY
		p.ProductID, p.ProductKey
) q
	LEFT OUTER JOIN
[ProducttoStoreLinkDetails] l
		ON l.ProductKey = q.ProductKey
WHERE l.DateCreated > '2012-12-31' and l.DateCreated < '2013-04-01' 
GROUP BY 
	q.ProductID, q.Views

```

---

<div class="post-metadata">

**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\
**Post date:** [April 5, 2013, 7:14pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/12 "2013-04-05T19:14:32Z")

</div>

**AnalogSignal** , your method can work, but you need to move your WHERE clauses up into the JOIN clause on the left outer (for both of them).

Otherwise, if rows exist for the product in DailyViews (for example), the LEFT OUTER JOIN finds a match and basically acts like a normal INNER JOIN, but the where clause then removes that row, so the ProductID is not returned at all.

Do this (WHERE switched to AND in join):

```auto

FROM 
    [Products] p 
        LEFT OUTER JOIN 
    [DailyViews] dv 
            ON dv.ProductID = p.ProductID 
      **AND** dv.ViewDate > '2012-12-31' and dv.ViewDate < '2013-04-01' 

```

---

<div class="post-metadata">

**Author:** ![bump](https://avatars.discourse-cdn.com/v4/letter/b/7c8e57/32.png) [@bump](https://boards.straightdope.com/u/bump)\
**Post date:** [April 5, 2013, 7:23pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/13 "2013-04-05T19:23:02Z")

</div>

> [@StephenG](#):
>
> Adding that, the query executes successfully and I get 0 results.
> 
> It turns out that ViewDate is in date format while DateCreated is in datetime format. I’m now looking up conversion methods…

convert(char(10),cast(ViewDate as datetime),101)

and/or

convert(char(10),cast(DateCreated as datetime),101)

should convert to “mm/dd/yyyy” format.

---

<div class="post-metadata">

**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\
**Post date:** [April 5, 2013, 9:23pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/14 "2013-04-05T21:23:29Z")

</div>

If this is SQL Server, no need to convert both fields to char, just convert the datetime as date.

CONVERT(Date, DateCreated)

Not sure about other DBMS.

---

<div class="post-metadata">

**Author:** ![AnalogSignal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/analogsignal/32/1085_2.png) [@AnalogSignal](https://boards.straightdope.com/u/AnalogSignal)\
**Post date:** [April 5, 2013, 11:28pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/15 "2013-04-05T23:28:33Z")

</div>

> [@RaftPeople](#):
>
> **AnalogSignal** , your method can work, but you need to move your WHERE clauses up into the JOIN clause on the left outer (for both of them).

Yes, good point! Agreed.

---

<div class="post-metadata">

**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\
**Post date:** [April 6, 2013, 8:32pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/16 "2013-04-06T20:32:00Z")

</div>

> [@AnalogSignal](#):
>
> Yes, good point! Agreed.

Genius! **AnalogSignal** , **RaftPeople** , this worked. Thank you, very, very much. I am heartily impressed by your skills, and I believe I have learned something. (At the very least, I’ll be saving this for future use and reference.)

---

<div class="post-metadata">

**Author:** ![AnalogSignal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/analogsignal/32/1085_2.png) [@AnalogSignal](https://boards.straightdope.com/u/AnalogSignal)\
**Post date:** [April 7, 2013, 5:11pm UTC](https://boards.straightdope.com/t/sql-help-sum-function/654780/17 "2013-04-07T17:11:36Z")

</div>

> [@StephenG](#):
>
> Genius! **AnalogSignal** , **RaftPeople** , this worked. Thank you, very, very much. I am heartily impressed by your skills, and I believe I have learned something. (At the very least, I’ll be saving this for future use and reference.)

I’m glad this worked. Thanks **StephenG** for letting us know. Several times I have posted a solution here only to have the OP never return. Thanks again to **RaftPeople** for providing the bug fix.
