| Author |
Topic |
|
cwfontan
Yak Posting Veteran
87 Posts |
Posted - 2009-09-03 : 11:56:47
|
I am trying to get percentage of moves from 1 state to other statestablecounts | start | Stop | state25 AL FL AL 25 AL TN AL 50 AL TX AL 2 AR AR AR 98 AR TX AR 15 CA CA CA 15 CA LA CA 60 CA NJ CA 10 CA TX CA 72 CO TX CO -------------------------------RESULT: 25 AL FL AL 25%25 AL TN AL 25%50 AL TX AL 50%2 AR AR AR 2%98 AR TX AR 98%15 CA CA CA 15%15 CA LA CA 15%60 CA NJ CA 60%10 CA TX CA 10%72 CO TX CO 100% This returns more than 1 in subselect.. I guess a union would be more of what im looking for?SELECT counts, start, stops, state, (convert(decimal(10,2),counts) * 100/convert(decimal(10,2),(SELECT SUM(counts) as qty FROM dbo.BOA_stateMoves GROUP BY start))) as pctFROM dbo.BOA_stateMovesGroup By counts, start, stops, stateORDER BY state, stops--set @divideby# = select sum(counts) as StateTotal Group by [start] from table |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-09-03 : 12:35:13
|
Here is one way:DECLARE @Table TABLE(counts INT, start CHAR(2), Stop CHAR(2), state CHAR(2))INSERT @TableSELECT 25, 'AL', 'FL', 'AL' UNION ALL SELECT 25, 'AL', 'TN', 'AL' UNION ALL SELECT 50, 'AL', 'TX', 'AL' UNION ALL SELECT 2, 'AR', 'AR', 'AR' UNION ALL SELECT 98, 'AR', 'TX', 'AR' UNION ALL SELECT 15, 'CA', 'CA', 'CA' UNION ALL SELECT 15, 'CA', 'LA', 'CA' UNION ALL SELECT 60, 'CA', 'NJ', 'CA' UNION ALL SELECT 10, 'CA', 'TX', 'CA' UNION ALL SELECT 72, 'CO', 'TX', 'CO' SELECT T.*, T.Counts * 1.00 / D.CountsFROM @Table AS TOUTER APPLY ( SELECT SUM(Counts) AS Counts FROM @Table WHERE Start = T.Start GROUP BY Start ) AS D |
 |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-09-03 : 12:50:22
|
and more:-- *** Test Data ***DECLARE @t TABLE( Counts int NOT NULL ,Start char(2) NOT NULL ,[Stop] char(2) NOT NULL)INSERT INTO @tSELECT 25, 'AL', 'FL' UNION ALLSELECT 25, 'AL', 'TN' UNION ALLSELECT 50, 'AL', 'TX' UNION ALLSELECT 2, 'AR','AR' UNION ALLSELECT 98, 'AR', 'TX' UNION ALLSELECT 15, 'CA', 'CA' UNION ALLSELECT 15, 'CA', 'LA' UNION ALLSELECT 60, 'CA', 'NJ' UNION ALLSELECT 10, 'CA', 'TX' UNION ALLSELECT 72, 'CO', 'TX'-- *** Test Data ***SELECT T.Counts, T.Start, T.[Stop] ,CAST(T.Counts * 100.0 / D.StateTotal AS decimal(10,2)) AS StartPercentFROM @t T JOIN ( SELECT T1.Start ,SUM(T1.Counts) AS StateTotal FROM @t T1 GROUP BY T1.Start ) D ON T.Start = D.Start -- orSELECT Counts, Start, [Stop] ,CAST(Counts * 100.0 / StateTotal AS decimal(10,2)) AS StartPercentFROM( SELECT Counts, Start, [Stop] ,SUM(Counts) OVER (PARTITION BY Start) AS StateTotal FROM @t) D-- or;WITH cte (Counts, Start, [Stop], StateTotal)AS( SELECT Counts, Start, [Stop] ,SUM(Counts) OVER (PARTITION BY Start) FROM @t)SELECT Counts, Start, [Stop] ,CAST(Counts * 100.0 / StateTotal AS decimal(10,2)) AS StartPercentFROM cte |
 |
|
|
|
|
|