SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to create loop without loop [sql] ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deathrone
Starting Member

Poland
15 Posts

Posted - 12/21/2012 :  04:24:02  Show Profile  Reply with Quote
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
52249 Posts

Posted - 12/21/2012 :  04:26:10  Show Profile  Reply with Quote
whats the primary key or unique valued column of table?

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  04:30:22  Show Profile  Reply with Quote

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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 12/21/2012 :  05:26:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  05:29:28  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  05:30:09  Show Profile  Reply with Quote

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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 12/21/2012 :  05:34:37  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000