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
 sql-ex.ru ex32
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yoyosh
Starting Member

27 Posts

Posted - 01/08/2013 :  03:49:21  Show Profile  Reply with Quote
Please give me at least hint what may be wrong in this query to ex.32:

One of characteristics of a ship is one-half cube of calibre of its main guns (mw). Within 2 decimal places, define the average value of mw for the ships of each country which has ships in database.

I tried:


SELECT DISTINCT Classes.country, convert(decimal(14,2), ( SELECT AVG( pen.p ) FROM
(
SELECT (c1.bore*c1.bore*c1.bore)/2 AS p FROM Classes AS c1, Ships AS s1
WHERE c1.class=s1.class AND c1.country = Classes.country
UNION ALL
SELECT (c2.bore*c2.bore*c2.bore)/2 FROM Classes AS c2, (select distinct ship from Outcomes) as o2
WHERE c2.country = Classes.country AND c2.class=o2.ship
AND o2.ship NOT IN ( SELECT ss.name FROM Ships AS ss )
) AS pen
WHERE pen.p IS NOT NULL
)) AS weight
FROM Classes
WHERE Classes.country IS NOT NULL



However this query produces one more record than expected (on second DB).

Thank you for help in advance

Edited by - yoyosh on 01/09/2013 01:22:12

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  03:53:34  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
average value for a country indicates that you need a group by on the country.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 01/08/2013 :  03:59:01  Show Profile  Reply with Quote
In my opinion grouping in subquery by country is handled by:

AND c1.country = Classes.country
and
c2.country = Classes.country
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 01/09/2013 :  01:14:09  Show Profile  Reply with Quote
Does anyone have an idea what could be wrong in my query?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/09/2013 :  03:21:55  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Why do you think that?
I thought the isue was that you were getting multiple reos so it sounds like it doesn't.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 01/09/2013 :  03:35:28  Show Profile  Reply with Quote
I didn't say I got multiple records. I just said I got more than required. So I am not filtering everything that I should
Go to Top of Page

gmb.dorr
Starting Member

USA
4 Posts

Posted - 01/28/2013 :  20:31:16  Show Profile  Reply with Quote
Hi yoyosh, have you solve this one already? I'm stuck here also.
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 01/29/2013 :  01:55:44  Show Profile  Reply with Quote
No. This is the only one that I cannot crack during first 60 exs.
Go to Top of Page

gmb.dorr
Starting Member

USA
4 Posts

Posted - 02/01/2013 :  00:04:33  Show Profile  Reply with Quote
Hi yoyosh, please email me gmb.dorr@gmail.com
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.26 seconds. Powered By: Snitz Forums 2000