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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 group sub select and find percentage by group

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 states


table
counts | start | Stop | state
25 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 pct
FROM dbo.BOA_stateMoves
Group By counts, start, stops, state
ORDER 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 @Table
SELECT 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.Counts
FROM
@Table AS T
OUTER APPLY
(
SELECT SUM(Counts) AS Counts
FROM @Table
WHERE Start = T.Start
GROUP BY Start
) AS D
Go to Top of Page

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 @t
SELECT 25, 'AL', 'FL' UNION ALL
SELECT 25, 'AL', 'TN' UNION ALL
SELECT 50, 'AL', 'TX' UNION ALL
SELECT 2, 'AR','AR' UNION ALL
SELECT 98, 'AR', 'TX' UNION ALL
SELECT 15, 'CA', 'CA' UNION ALL
SELECT 15, 'CA', 'LA' UNION ALL
SELECT 60, 'CA', 'NJ' UNION ALL
SELECT 10, 'CA', 'TX' UNION ALL
SELECT 72, 'CO', 'TX'
-- *** Test Data ***

SELECT T.Counts, T.Start, T.[Stop]
,CAST(T.Counts * 100.0 / D.StateTotal AS decimal(10,2)) AS StartPercent
FROM @t T
JOIN
(
SELECT T1.Start
,SUM(T1.Counts) AS StateTotal
FROM @t T1
GROUP BY T1.Start
) D
ON T.Start = D.Start

-- or
SELECT Counts, Start, [Stop]
,CAST(Counts * 100.0 / StateTotal AS decimal(10,2)) AS StartPercent
FROM
(
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 StartPercent
FROM cte

Go to Top of Page
   

- Advertisement -