Top 6 - 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)

Top 6

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-11-21 : 05:44:42

Hi
I'm trying to create a top 6 which I've managed to work out. I need to show the top 6 totals and also the all total for category, and then work out the percentage of the top 6 of the total category. I have the 2 queries below:

I need to first sum all top 6 and all categories(can this be included in the statements?) and then divide the top 6 by the total for all.

Help appreciated.

--return top 6 only
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
Order By TotalCat Desc

--return all
Select c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
Order By TotalCat Desc

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-21 : 06:31:02

You asked for 'top 6 totals', but you are calculating number of ids in each category.

Can you post sample data of DIM_CALL table? and also expected result. It will be useful to get quick response

--
Chandu

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-11-21 : 06:38:54

Yes its counting the total ID (tickets) for each category which is correct.

Top 6 results returns this:
Applications 113
PrintersD 111
CentralArc 95
LocalM 62
Server 53
Wins 23

The all query returns same outage as above but more records because its based on ALL categories.

I have the 2 tables now but its the cross referencing part to get the sum(top6\all) I'm having issues with?

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-21 : 07:02:01

[code]
;with AllCat
as (Select c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident' And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
),
Top6Cat
AS (SELECT top 6 INTI_CATEGORY, TotalCat
from AllCat
ORDER BY TotalCat DESC
)
SELECT CAST((select SUM(TotalCat)*100 FROM Top6Cat)/(select CAST( SUM(TotalCat) AS DEC(10,2)) FROM AllCat) AS DEC(10,2)) as Percentage[/code]

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-11-21 : 07:44:16

Agh thats slick and what I was after in the same query, so we can use the With staement here then for referencing same query, nice...to show percentage as say 75.5% is this easy enough?

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-21 : 08:12:24

Check the modified Post ( RED marked) for getting decimal part...

--
Chandu

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-11-21 : 08:17:54

Thanks Chandu

Very helpful that...:)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-21 : 08:21:50

quote:
Originally posted by sz1

Thanks Chandu

Very helpful that...:)

Welcome

--
Chandu

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-11-21 : 08:30:06

Chandu

Just one thing is it simple enough to add columns to the query to show the actual category and count of ID to? on the report I only see Percentage field.

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-21 : 09:10:36

[code]
;with AllCat
as (Select c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident' And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
),
Top6Cat
AS (SELECT top 6 INTI_CATEGORY, TotalCat
from AllCat
ORDER BY TotalCat DESC
),
Percentage
AS (
SELECT CAST((select SUM(TotalCat)*100 FROM Top6Cat)/(select CAST( SUM(TotalCat) AS DEC(10,2)) FROM AllCat) AS DEC(10,2)) AS per
)
SELECT distinct a.INTI_CATEGORY, a.TotalCat, c.per
FROM AllCat a, Percentage c

[/code]
--
Chandu

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-11-21 : 09:57:54

Thanks I need some more practice...:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources