sql-ex.ru ex32 - 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

General SQL Server Forums

New to SQL Server Programming

sql-ex.ru ex32

Author

Topic

yoyosh
Starting Member

27 Posts

Posted - 2013-01-08 : 03:49:21

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 03:53:34

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.

yoyosh
Starting Member

27 Posts

Posted - 2013-01-08 : 03:59:01

In my opinion grouping in subquery by country is handled by:

AND c1.country = Classes.country
and
c2.country = Classes.country

yoyosh
Starting Member

27 Posts

Posted - 2013-01-09 : 01:14:09

Does anyone have an idea what could be wrong in my query?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-09 : 03:21:55

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.

yoyosh
Starting Member

27 Posts

Posted - 2013-01-09 : 03:35:28

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

gmb.dorr
Starting Member

4 Posts

Posted - 2013-01-28 : 20:31:16

Hi yoyosh, have you solve this one already? I'm stuck here also.

yoyosh
Starting Member

27 Posts

Posted - 2013-01-29 : 01:55:44

No. This is the only one that I cannot crack during first 60 exs.