Query Question - SUM
in [Programming SQL]
|
Prev: images in select query
Next: Build a date value
From: sChapman on 4 May 2010 10:45 Please see the sql code below. What I am after is Sum of [RiskCount]. But due to the join, I get double counting and consequently wrong SUM. Is this an inherent limitaion of Sql? Can be it solved without breaking up the sql into two parts? Thanks for reading this post. CREATE TABLE #Location ( LocationID INT, RiskCount INT) CREATE TABLE #Coverage ( LocationID INT, Peril VARCHAR(10), Coverage VARCHAR(10), TSI Numeric(25,6)) INSERT INTO #Location VALUES (1,1) INSERT INTO #Location VALUES (2,1) INSERT INTO #Location VALUES (3,1) INSERT INTO #Location VALUES (4,4) INSERT INTO #Location VALUES (5,5) INSERT INTO #Coverage VALUES(1,'EQ','B',11) INSERT INTO #Coverage VALUES(2,'EQ','B',12) INSERT INTO #Coverage VALUES(3,'EQ','B',13) INSERT INTO #Coverage VALUES(4,'EQ','B',14) INSERT INTO #Coverage VALUES(5,'EQ','B',15) INSERT INTO #Coverage VALUES(1,'EQ','C',21) INSERT INTO #Coverage VALUES(2,'EQ','C',22) INSERT INTO #Coverage VALUES(3,'EQ','C',23) INSERT INTO #Coverage VALUES(4,'EQ','C',24) INSERT INTO #Coverage VALUES(5,'EQ','C',25) SELECT COUNT(DISTINCT L.LocationID) AS [LocRowCount], SUM(L.RiskCount) AS [RiskCount], SUM(C.TSI) AS [TSI] FROM #Location L INNER JOIN #Coverage C ON C.LocationID = L.LocationID DROP TABLE #Location DROP TABLE #Coverage
From: Tom Cooper on 4 May 2010 11:11 One way (this assumes that LocationID is unique in the #Location table, I'm guessing it is the primary key of the #Location table) ;WITH cte AS (SELECT L.LocationID, L.RiskCount, C.TSI, ROW_NUMBER() OVER (PARTITION BY L.LocationID ORDER BY L.LocationID) AS rn FROM #Location L INNER JOIN #Coverage C ON C.LocationID = L.LocationID) SELECT SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) AS [LocRowCount], SUM(CASE WHEN rn = 1 THEN RiskCount ELSE 0 END) AS [RiskCount], SUM(TSI) AS [TSI] FROM cte; Tom "sChapman" <sumanthcp(a)googlemail.com> wrote in message news:b3b4ff3b-2a8d-4a11-8e08-7a434c65f08a(a)b18g2000yqb.googlegroups.com... > Please see the sql code below. What I am after is Sum of [RiskCount]. > But due to the join, I get double counting and consequently wrong SUM. > Is this an inherent limitaion of Sql? Can be it solved without > breaking up the sql into two parts? Thanks for reading this post. > > CREATE TABLE #Location ( > LocationID INT, > RiskCount INT) > CREATE TABLE #Coverage ( > LocationID INT, > Peril VARCHAR(10), > Coverage VARCHAR(10), > TSI Numeric(25,6)) > > INSERT INTO #Location VALUES (1,1) > INSERT INTO #Location VALUES (2,1) > INSERT INTO #Location VALUES (3,1) > INSERT INTO #Location VALUES (4,4) > INSERT INTO #Location VALUES (5,5) > > INSERT INTO #Coverage VALUES(1,'EQ','B',11) > INSERT INTO #Coverage VALUES(2,'EQ','B',12) > INSERT INTO #Coverage VALUES(3,'EQ','B',13) > INSERT INTO #Coverage VALUES(4,'EQ','B',14) > INSERT INTO #Coverage VALUES(5,'EQ','B',15) > > INSERT INTO #Coverage VALUES(1,'EQ','C',21) > INSERT INTO #Coverage VALUES(2,'EQ','C',22) > INSERT INTO #Coverage VALUES(3,'EQ','C',23) > INSERT INTO #Coverage VALUES(4,'EQ','C',24) > INSERT INTO #Coverage VALUES(5,'EQ','C',25) > > > SELECT COUNT(DISTINCT L.LocationID) AS [LocRowCount], > SUM(L.RiskCount) AS [RiskCount], > SUM(C.TSI) AS [TSI] > FROM #Location L > INNER JOIN #Coverage C ON C.LocationID = L.LocationID > > DROP TABLE #Location > DROP TABLE #Coverage
From: BruceL on 4 May 2010 11:35 On May 4, 10:45 am, sChapman <sumant...(a)googlemail.com> wrote: > Please see the sql code below. What I am after is Sum of [RiskCount]. > But due to the join, I get double counting and consequently wrong SUM. > Is this an inherent limitaion of Sql? Can be it solved without > breaking up the sql into two parts? Thanks for reading this post. > > CREATE TABLE #Location ( > LocationID INT, > RiskCount INT) > CREATE TABLE #Coverage ( > LocationID INT, > Peril VARCHAR(10), > Coverage VARCHAR(10), > TSI Numeric(25,6)) > > INSERT INTO #Location VALUES (1,1) > INSERT INTO #Location VALUES (2,1) > INSERT INTO #Location VALUES (3,1) > INSERT INTO #Location VALUES (4,4) > INSERT INTO #Location VALUES (5,5) > > INSERT INTO #Coverage VALUES(1,'EQ','B',11) > INSERT INTO #Coverage VALUES(2,'EQ','B',12) > INSERT INTO #Coverage VALUES(3,'EQ','B',13) > INSERT INTO #Coverage VALUES(4,'EQ','B',14) > INSERT INTO #Coverage VALUES(5,'EQ','B',15) > > INSERT INTO #Coverage VALUES(1,'EQ','C',21) > INSERT INTO #Coverage VALUES(2,'EQ','C',22) > INSERT INTO #Coverage VALUES(3,'EQ','C',23) > INSERT INTO #Coverage VALUES(4,'EQ','C',24) > INSERT INTO #Coverage VALUES(5,'EQ','C',25) > > SELECT COUNT(DISTINCT L.LocationID) AS [LocRowCount], > SUM(L.RiskCount) AS [RiskCount], > SUM(C.TSI) AS [TSI] > FROM #Location L > INNER JOIN #Coverage C ON C.LocationID = L.LocationID > > DROP TABLE #Location > DROP TABLE #Coverage You show no interest in the coverage location, so just subquery that total. SELECT count(DISTINCT L.LocationID) AS [LocRowCount], sum(L.RiskCount) AS [RiskCount], (select sum(TSI) from #coverage) AS [TSI] FROM #Location L
From: sChapman on 4 May 2010 11:59 Thanks for looking into the post. I do need Coverage and the query I have posted is only an illustration of the double counting problem. The final query is much more complicated with many filters on Coverage and Location tables. The problem looks like a very common one in a Parent Child relationship situation, that's why I am trying to find out if I am missing something! I am trying not to use CTE or temporary tables. We have around 500+ queries that look very similar to the one posted so I am trying to establish the right (and simple) method before we start writing the queries. Every query has to be self contained (i.e can't create temporary tables and reuse them) as the queries run inside a 3rd party tool.
From: BruceL on 4 May 2010 15:23
On May 4, 11:59 am, sChapman <sumant...(a)googlemail.com> wrote: > Thanks for looking into the post. I do need Coverage and the query I > have posted is only an illustration of the double counting problem. > The final query is much more complicated with many filters on Coverage > and Location tables. > > The problem looks like a very common one in a Parent Child > relationship situation, that's why I am trying to find out if I am > missing something! I am trying not to use CTE or temporary tables. We > have around 500+ queries that look very similar to the one posted so I > am trying to establish the right (and simple) method before we start > writing the queries. Every query has to be self contained (i.e can't > create temporary tables and reuse them) as the queries run inside a > 3rd party tool. So, do you want the sum of all coverage or the sum of coverage by location? |