| Author |
 Topic  |
|
|
deathrone
Starting Member
Poland
9 Posts |
 Posted - 12/21/2012 : 04:24:02
|
i've got f.x. sth like this:
First column: Morderer_id.
Second column: shoots_No
Third columnt: victim_id
01 1 A
01 2 A
01 3 A
01 kill A
01 1 B
01 2 B
01 1 C
01 kill C
02 1 D
02 2 D
02 3 D
02 4 D
02 kill D
02 1 E
02 1 F
02 1 G
02 2 G
AND I would like to count how much avg( shoots ) the morderer needs to kill victim. For example to kill victim A morderer 01 needed 4 shoots to kill C only 2.
Im tryin to make this with lag statement but Im afraid iits not enoygh. Please Help good people!
trololoolo |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/21/2012 : 04:26:10
|
whats the primary key or unique valued column of table?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/21/2012 : 04:30:22
|
SELECT Morderer_Id,SUM(ShootCnt)*1.0/COUNT(*) AS AvgShoots
FROM
(
SELECT Morderer_id,victim_id,SUM(CASE WHEN shoot_no <> 'Kill' THEN 1 ELSE 0 END) AS ShootCnt
FROM table
GROUP BY Morderer_id,victim_id
HAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0
)t
GROUP BY Morderee_Id
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/21/2012 : 05:26:13
|
Hi visakh,
>>to kill victim A morderer 01 needed 4 shoots to kill C only 2.
Your inner query returning noOfShoots as 3 only for morderer 01(for A victim) ANd 1 for C
I mean,
You missed that last attempt count there...
So it should be
SUM(CASE WHEN shoot_no <> 'Kill' THEN 1 ELSE 0 END)+1 AS ShootCnt
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/21/2012 : 05:29:28
|
quote:
Originally posted by bandi
Hi visakh,
>>to kill victim A morderer 01 needed 4 shoots to kill C only 2.
Your inner query returning noOfShoots as 3 only for morderer 01(for A victim) ANd 1 for C
I mean,
You missed that last attempt count there...
So it should be
SUM(CASE WHEN shoot_no <> 'Kill' THEN 1 ELSE 0 END)+1 AS ShootCnt
--
Chandu
in that case no need of +1
just COUNT(*) would do
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/21/2012 : 05:30:09
|
SELECT Morderer_Id,SUM(ShootCnt)*1.0/COUNT(*) AS AvgShoots
FROM
(
SELECT Morderer_id,victim_id,COUNT(*) AS ShootCnt
FROM table
GROUP BY Morderer_id,victim_id
HAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0
)t
GROUP BY Morderee_Id
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/21/2012 : 05:34:37
|
quote:
Originally posted by visakh16
SELECT Morderer_Id,SUM(ShootCnt)*1.0/COUNT(*) AS AvgShoots
FROM
(
SELECT Morderer_id,victim_id,COUNT(*) AS ShootCnt
FROM table
GROUP BY Morderer_id,victim_id
HAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0
)t
GROUP BY Morderee_Id
Now looks nice
--
Chandu |
|
|
| |
Topic |
|
How to create loop without loop [sql] ?
