| Author |
 Topic  |
|
|
sz1
Aged Yak Warrior
United Kingdom
555 Posts |
 Posted - 01/20/2014 : 10:30:41
|
Hi
Im trying to add up all the rows for each category which Ive done below but then I want to only show in the CTE the total of all categories and not all the counts, so just the max total for each one.
The CTE is showing more than one category per max count on the Max(MyRank). Is there a way to keep the CTE but get it to show me only the max total for each single category?
Thanks
Code to work out totals
SELECT
[TypeOfIncident]
,[Category]
,ROW_NUMBER()OVER (PARTITION BY [Category] order by [Category]) 'MyRank'
--,[SubCategory]
FROM [dbo].[Incident]
WHERE [CreatedDateTime] BETWEEN '2013-01-20 00:00:00' AND '2014-01-20 23:59:59'
and [TypeOfIncident] = 'Failure'
Code to work out Max
USE Test
go
WITH CTE_MyRank
as
(
SELECT
[TypeOfIncident]
,[Category]
,ROW_NUMBER()OVER (PARTITION BY [Category] order by [Category]) 'MyRank'
--,[SubCategory]
FROM [dbo].[Incident]
WHERE [CreatedDateTime] BETWEEN '2013-01-20 00:00:00' AND '2014-01-20 23:59:59'
and [TypeOfIncident] = 'Failure'
)
SELECT TypeOfIncident,
(select distinct[Category]),
Max(MyRank) as MaxTotal
FROM CTE_MyRank
GROUP BY
[TypeOfIncident],
[Category],
[MyRank]
)
SZ1
Please help me to enable me to help others! |
|
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 01/20/2014 : 22:29:04
|
can you post some sample data and the expected result ?
KH
Time is always against us
|
 |
|
|
sz1
Aged Yak Warrior
United Kingdom
555 Posts |
Posted - 01/21/2014 : 05:04:54
|
Sure I can get the max for each by using a count of and group by but wanted to know if I can do the same on a max Row_Number as shown below, currently the MaxTotal is returning a 1 instead of the actual max per category.
Id like to see the MaxTotal return the same as Category Total?
Ta
USE Test
go
WITH CTE_MyRank
as
(
SELECT
[TypeOfIncident]
,Count([Category]) 'Category'
,ROW_NUMBER()OVER (PARTITION BY [Category] order by [Category]) 'MyRank'
--,[SubCategory]
FROM [dbo].[Incident]
WHERE [CreatedDateTime] BETWEEN '2013-01-20 00:00:00' AND '2014-01-20 23:59:59'
and [TypeOfIncident] = 'Failure'
Group By [TypeOfIncident],
[Category]
)
SELECT TypeOfIncident,
[Category],
max(MyRank) as MaxTotal
FROM CTE_MyRank
GROUP BY
[TypeOfIncident],
[Category],
[MyRank]
--ORDER BY MyRank;
TypeOfIncident Category MaxTotal
Failure 1 1
Failure 2 1
Failure 14 1
Failure 26 1
Failure 347 1
Failure 748 1
Failure 1261 1
Failure 1289 1
Failure 1623 1
Failure 2877 1
Failure 3303 1
Failure 3394 1
SZ1
Please help me to enable me to help others! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
|
|
sz1
Aged Yak Warrior
United Kingdom
555 Posts |
Posted - 01/21/2014 : 08:54:00
|
The same figure to the left of the 1 values, when you run the query without the CTE the rows will add numbers tallying to the amount per category, so I thought I could return the Max of each row number max output. SO if there are 10 oranges then I would see 1,2,3,4,5,6,7,8,9,10 and if 3 apples I would see 1,2,3, what I want to see in the column would be 10 and 3. This works with the count and group by as seen on the output but the right column with values of 1 I want the same output if this is possible. Its really for future use as it works with the count...thanks
SZ1
Please help me to enable me to help others! |
|
|
| |
Topic |
|
|
|
Max(Row_Number()