Count with select top - 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 2000 Forums

Transact-SQL (2000)

Count with select top

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-07-27 : 04:27:08

I have the following tables (non essential columns left out)

Risks
risk_id (pk)

Hazards
hadard_id (pk)
riskid (joined to risk_id on above table)
level_with (can be either L, M, H, E)
L = low
M = medium
H = high
E = extreme

Now, each risk can have multiple hazards against it, but here's the hard part (for me).

I need to produce a query that gives the following

Extreme = 2
High = 50
Medium = 34
etc

But here's the hard part for me. With each risk able to have multiple hazards against it, I only want to include the highest level hazard in the count.

For example, if a risk has 4 hazards associated with it, 2 Low, 1 Medium and 1 High, the count should only count the High one

If a risk has 2 hazards associated with it, 1 high and 1 extreme, only the extreme one would be counted

Does that make sense?

I dont have to have one single query to produce the table because I can run it 4 times if need be, one for Low, one for Medium etc

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-27 : 05:08:18

have a go with below for starters......(to debug....work from the inside outwards)

select a.risk_id, b.max_score, count(*) from
risks a
left join
(
select riskid, max(level_score) as max_score from
(select
riskid,
case level_with
when "L" then 1
when "M" then 2
when "H" then 3
when "E" then 4
end case as level_score
from hazard
) a
) b on b.riskid = a.risk_id
group by a.risk_id, b.max_score

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-07-27 : 06:07:35

Thanks, but I can't get it to work properly. I rewrote it slightly with the correct column names to this

select a.risk_id, b.max_score, count(*) from
risks a
left join
(
select riskid, max(level_score) as max_score from
(select
riskid,
case level_with
when 'L' then 1
when 'M' then 2
when 'H' then 3
when 'E' then 4
end as level_score
from risk_hazards
) a
) b on b.riskid = a.risk_id
group by a.risk_id, b.max_score

But I get this error

Column 'a.riskid' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-07-27 : 07:45:19

[code]
SELECT R.RiskLevel, COUNT(*) AS LevelCount
FROM
(
SELECT 'Low', 1
UNION ALL SELECT 'Medium', 2
UNION ALL SELECT 'High', 3
UNION ALL SELECT 'Extreme', 4
) R (RiskLevel, RiskNo)
LEFT JOIN
(
SELECT riskid, MAX(CHARINDEX(level_with, 'LMHE')) AS RiskNo
FROM risk_hazards
GROUP BY riskid
) D
ON R.RiskNo = D.RiskNo
GROUP BY R.RiskLevel
ORDER BY R.RiskNo DESC
[/code]

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-07-27 : 07:50:23

Ifor, when I run that I get:

Column name 'R.RiskNo' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-07-27 : 07:52:46

It's ok, I changed the last line to GROUP BY R.RiskLevel, R.RiskNo and it seems to work, just testing...

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-07-27 : 08:29:52

also, I now realise I need to be able to view these figures by department and date added. The risks table has a column called deptid and a date column called assessment_date. Is there any way I can add to the query

where deptid = 5 and assessment_date between '2010-01-01' and '2010-02-01' or something like that?

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-07-28 : 04:34:19

Anyone?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-28 : 06:53:14

select a,b,c,count(*) from mytable group by a,b,c is the general format of this type of construct.

going by the above i don't see why you need "GROUP BY R.RiskLevel, R.RiskNo"...it should just be "GROUP BY R.RiskLevel, ORDER BY R.RiskLevel"

Also re dept and date....consider then as b,c in the general construct above.

Finally....it might be time to invest some time in BOL or a training course. You're now moving beyond Beginners SQL and into Intermediate SQL.....upskilling formally is often the best way to solving these problems permanently and also for achieving a payrise!

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-07-28 : 07:25:56

I agree, however trying to get money for courses etc at this place is a nightmare. I'm expected to do everything.

Any chance you can help?

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-08-05 : 12:02:52

Anyone?

Kokkula
Starting Member

41 Posts

Posted - 2010-08-09 : 06:31:14

Hello,

Try this....

DECLARE @TABLE TABLE (RID INT, HID INT, LEVEL NVARCHAR(1))

INSERT INTO @TABLE VALUES (1, 1, 'E')
INSERT INTO @TABLE VALUES (1, 2, 'E')
INSERT INTO @TABLE VALUES (1, 3, 'H')
INSERT INTO @TABLE VALUES (2, 1, 'M')
INSERT INTO @TABLE VALUES (3, 1, 'H')
INSERT INTO @TABLE VALUES (3, 2, 'E')
INSERT INTO @TABLE VALUES (3, 3, 'H')
INSERT INTO @TABLE VALUES (4, 1, 'L')
INSERT INTO @TABLE VALUES (4, 2, 'L')
INSERT INTO @TABLE VALUES (4, 3, 'L')

SELECT CASE C.LEVEL
WHEN 1 THEN 'Low:'
WHEN 2 THEN 'Medium:'
WHEN 3 THEN 'Hight:'
WHEN 4 THEN 'Extreme'
END AS LEVEL
, COUNT( DISTINCT B.RID) AS Cnt
FROM @TABLE B
INNER JOIN
(
SELECT RID,
MAX(case LEVEL
when 'L' then 1
when 'M' then 2
when 'H' then 3
when 'E' then 4
end) as LEVEL
FROM @TABLE
GROUP BY RID
) C ON B.RID = C.RID AND B.LEVEL = CASE C.LEVEL
WHEN 1 THEN 'L'
WHEN 2 THEN 'M'
WHEN 3 THEN 'H'
WHEN 4 THEN 'E'
END
GROUP BY C.LEVEL

Hope its helpful....

Pavan
Infosys Technologies Limited

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources