SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Conditional Sum Based on Count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BTrain
Starting Member

2 Posts

Posted - 10/15/2013 :  23:18:36  Show Profile  Reply with Quote
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

India
169 Posts

Posted - 10/16/2013 :  01:02:12  Show Profile  Reply with Quote
Provide Sample Data

veeranjaneyulu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2013 :  02:05:48  Show Profile  Reply with Quote
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
Go to Top of Page

BTrain
Starting Member

2 Posts

Posted - 10/28/2013 :  17:06:32  Show Profile  Reply with Quote
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!

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 10/28/2013 :  17:29:01  Show Profile  Reply with Quote
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;
	
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2013 :  23:50:19  Show Profile  Reply with Quote
IS [%Total Sales] a calculated column in the query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000