Trouble with SUM

Question

Trouble with SUM

vecerda

SSCrazy

Points: 2532
More actions
December 11, 2015 at 6:24 am

#307004

Hi there,
I have this query:
SELECT filteredfs_franchise.fs_franchisenumber, dbo.Filteredfs_carvelgmr.fs_year AS Year, SUM(COALESCE(FS_Amount, '0.00')) AS MTD
FROM filteredfs_carvelgmr
INNER JOIN filteredfs_franchise ON filteredfs_carvelgmr.fs_franchiseid = filteredfs_franchise.fs_franchiseid
INNER JOIN dbo.Filteredfs_carvelgmrcalendar ON filteredfs_carvelgmr.fs_year = dbo.Filteredfs_carvelgmrcalendar.fs_year
INNER JOIN [Staging_FCRM_to_AX].dbo.GMR_Calendar_Year_Translation cal ON cal.Period = filteredfs_carvelgmr.fs_period
AND cal.Year = filteredfs_carvelgmr.fs_year
WHERE MONTH(cal.Date) = 11
AND dbo.Filteredfs_carvelgmr.fs_year IN
('2015', '2014')
AND filteredfs_franchise.fs_franchisenumber = '2248'
AND fs_type = '717610004'
AND filteredfs_franchise.fs_brandidname LIKE 'Carvel%'
GROUP BY dbo.Filteredfs_carvelgmr.fs_year, filteredfs_franchise.fs_franchisenumber;
I have hardcoded the values just for the purposes of this example (the value for year and store number).
The query returns this result fs_franchisenumberYearMTD
2248 2014425.00
I need it to also return the values of 2248 2015 0.00 as there is no data for November of 2015.
Not sure how I would go about that. Any help is greatly appreciated. I thought the COALESCE statement would take care of it, but it does not.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are there rows for november in your calendar table dbo.Filteredfs_carvelgmrcalendar?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vecerda SSCrazy Points: 2532 More actions · Answer 2

there are none, which I know is where the problem is. But I am not sure how to change the query to translate no rows in the source table into a sum of zero.

thanks,

Petr

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 3

Create a tally table with all needed dates and left join it instead of inner join.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

Generally, create a set of keys (year +month i suppose) to collect data about and left join data collection query to this set.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Then you're going to need to create a calendar table that has all months in it (and just columns related to the dates) and query that table LEFT JOIN to your tables that don't have the november rows in them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

vecerda (12/11/2015)
there are none, which I know is where the problem is. But I am not sure how to change the query to translate no rows in the source table into a sum of zero.
thanks,
Petr

I have created a simplified version of what you data looks like using table variables. If you play around with this code a little you will easily figure out your problem.

Sample Data:

DECLARE @datetable TABLE (calyr smallint);

DECLARE @franchisee TABLE (fs_num int primary key, fs_name varchar(100));

DECLARE @amounts TABLE (fs_year smallint, fs_num int, fs_amount int);

INSERT @datetable VALUES (2013),(2014),(2015),(2016);

INSERT @franchisee VALUES (1,'tim'),(2,'sue'),(3,'leroy');

INSERT @amounts VALUES

(2014,1,100),(2014,1,100),(2015,1,200),(2015,1,200), -- franchisee 1 had 200 in 2014, 400 in 2015

(2014,2,500),(2014,2,250),(2014,2,100); -- franchisee 2 had 850 in 2014, nothing in 2015

Let's say we wanted the amount for franchisee 1 & 2 for years 2014 & 2015....

Step 1: Use a Cross Join to get all the franchisee's and all years you want to measure

SELECT f.fs_num, d.calyr

FROM @franchisee f

CROSS JOIN @datetable d

WHERE f.fs_num IN (1,2) AND d.calyr IN (2014,2015);

Results:

fs_num calyr

----------- ------

1 2014

2 2014

1 2015

2 2015

Step 2: LEFT JOIN to the @amounts on fs_year and @franchisee on fs_num, add the fs_amount column

fs_num calyr fs_amount

----------- ------ -----------

1 2014 100

1 2015 200

2 2014 500

2 2014 250

2 2014 100

2 2015 NULL

Step 3: SUM your amount column, COALESCE for 0

(including the sample data DDL so you can run this)

DECLARE @datetable TABLE (calyr smallint);

DECLARE @franchisee TABLE (fs_num int primary key, fs_name varchar(100));

DECLARE @amounts TABLE (fs_year smallint, fs_num int, fs_amount int);

SELECT f.fs_num, d.calyr, SUM(COALESCE(a.fs_amount,0)) AS MTD

FROM @franchisee f

CROSS JOIN @datetable d

LEFT JOIN @amounts a ON d.calyr = a.fs_year AND f.fs_num = a.fs_num

WHERE f.fs_num IN (1,2) AND d.calyr IN (2014,2015)

GROUP BY f.fs_num, d.calyr;

Results:

fs_num calyr MTD

----------- ------ -----------

1 2014 200

1 2015 400

2 2014 850

2 2015 0

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001