Conditional Sum Based on Count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conditional Sum Based on Count

Author

Topic

BTrain
Starting Member

2 Posts

Posted - 2013-10-15 : 23:18:36

Hello,

I have reviewed the various 'Conditional Sum' questions in the forum and none quite match what I'm trying to do:

In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)

In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.

I cannot figure out how to do this. Basically, I would like to implement logic that would work like this:

SELECT Date, Store#, Item#,
CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales)
ELSE %Total Sales
END

FROM (some tables and joins)
GROUP BY Date, Store#, Item#

However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).

Any help would be appreciated.

Thank you!

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-16 : 01:02:12

Provide Sample Data

veeranjaneyulu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:05:48

As per your explanation you just need this


SELECT [Date], [Store#], [Item#], 
Sum([%Total Sales]) 
FROM (some tables and joins)
GROUP BY [Date], [Store#], [Item#]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

BTrain
Starting Member

2 Posts

Posted - 2013-10-28 : 17:06:32

Unfortunately, I am still having trouble making this work. Please see the sample data below. Basically, I just want to sum the percentage values only where an item was given more than one percentage:

CURRENT OUTPUT:
Date Store# Item# %Total Sales
10/25/2013 50 123 .05
10/25/2013 50 123 .08
10/25/2013 50 456 .20
10/25/2013 50 789 .03

DESIRED OUTPUT:
Date Store# Item# %Total Sales
10/25/2013 50 123 .13
10/25/2013 50 456 .20
10/25/2013 50 789 .03

Any help is appreciated! Thank you!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 17:29:01

You can sum even if there is only one row - it will just sum that one row, resulting in the same value. See the example below. You can copy and paste it to an SSMS window and run it - you will see that it gives you exactly the output you are looking for:

CREATE TABLE #tmp(Date DATETIME, Store INT, Item INT, PercentTotalSales FLOAT);
INSERT INTO #tmp VALUES 
('20131025',50,123,0.05),
('20131025',50,123,0.08),
('20131025',50,456,0.20),
('20131025',50,789,0.03)

SELECT Date,STore, Item, SUM(PercentTotalSales) AS PercentTotalSales
FROM #tmp
GROUP BY  Date,STore, Item

DROP TABLE #tmp;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 23:50:19

IS [%Total Sales] a calculated column in the query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources