Percentage of each row count - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Percentage of each row count

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-10-14 : 14:53:26

I want to add a column of a percentage of the total counts for each record. How do I do this. Here is the code so far

SELECT COUNT(dbo.OffenderType_Lookup.ID) AS Count, dbo.OffenderType_Lookup.Description AS Type, 'Active' AS Status
FROM dbo.Offender INNER JOIN
dbo.NCIC_OffenderStatus ON dbo.Offender.OffenderStatus = dbo.NCIC_OffenderStatus.ID LEFT OUTER JOIN
dbo.OffenderType_Lookup ON dbo.Offender.OffenderType = dbo.OffenderType_Lookup.ID
WHERE (dbo.NCIC_OffenderStatus.Description LIKE N'Active%')
GROUP BY dbo.OffenderType_Lookup.ID, dbo.OffenderType_Lookup.Description
ORDER BY type

Dave
Helixpoint Web Development
http://www.helixpoint.com

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-14 : 20:00:57

;with Subtotals as (
SELECT COUNT(dbo.OffenderType_Lookup.ID) AS Count, dbo.OffenderType_Lookup.Description AS Type, 'Active' AS Status
FROM dbo.Offender INNER JOIN
dbo.NCIC_OffenderStatus ON dbo.Offender.OffenderStatus = dbo.NCIC_OffenderStatus.ID LEFT OUTER JOIN
dbo.OffenderType_Lookup ON dbo.Offender.OffenderType = dbo.OffenderType_Lookup.ID
WHERE (dbo.NCIC_OffenderStatus.Description LIKE N'Active%')
GROUP BY dbo.OffenderType_Lookup.ID, dbo.OffenderType_Lookup.Description
),
Totals as (
select sum(Count) Total
from Subtotals
)
select s.Count, 100.0 * s.Count / t.Total as PctCount, s.Type, s.Status
from SubTotals s
cross join
Totals t
order by s.type

(Untested)

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-15 : 02:10:43

[code]
SELECT COUNT(dbo.OffenderType_Lookup.ID) AS Count, dbo.OffenderType_Lookup.Description AS Type, 'Active' AS Status,
COUNT(dbo.OffenderType_Lookup.ID)*100.0/COUNT(dbo.OffenderType_Lookup.ID) OVER () AS [% Total Count]
FROM dbo.Offender INNER JOIN
dbo.NCIC_OffenderStatus ON dbo.Offender.OffenderStatus = dbo.NCIC_OffenderStatus.ID LEFT OUTER JOIN
dbo.OffenderType_Lookup ON dbo.Offender.OffenderType = dbo.OffenderType_Lookup.ID
WHERE (dbo.NCIC_OffenderStatus.Description LIKE N'Active%')
GROUP BY dbo.OffenderType_Lookup.ID, dbo.OffenderType_Lookup.Description
ORDER BY type
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources