how to do this? - 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

how to do this?

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 16:49:59

QUESTION 8
You are a database developer and you have many years experience in database development. Now you are
employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data
is stored in
the SQL Server 2008 database. There's a table which is named Commodities in the database. The
Commodities table has a column which is named Shape. Now you have got an order from your company
manager, according to his
requirement, you have to calculate the percentage of commodities of each commodity shape. So a Transact-
SQL statement has to be written to perform this task. Of the following Transact-SQL statement, which one
should be used?
A. SELECT Shape, (COUNT(*) * 1.0)/ COUNT(*) OVER() AS PercentShapeFROM CommoditiesGROUP BY
Shape;
B. SELECT Shape COUNT(*) * 1.0) / COUNT(*) OVER(PARTITION BY Shape) AS PercentShapeFROM
CommoditiesGROUP BY Shape;
C. SELECT Shape COUNT(*) OVER(PARTITION BY Shape) / (COUNT(*) * 1.0) AS PercentShapeFROM
CommoditiesGROUP BY Shape;
D. SELECT Shape COUNT(*) OVER() / (COUNT(*) * 1.0) AS PercentShape / (COUNT(*) * 1.0) AS
PercentShapeFROM CommoditiesGROUP BY Shape;

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 16:54:33

i can do it like this
with t as
(select COUNT(*) as tot from x)
select RAN, cast(COUNT(*)*100.00/t.tot as float) as 'percent' from t cross apply x group by ran,t.tot

but i am confused with the choices given

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-23 : 17:46:04

As far as I can tell none of those are correct. Could be typos.?

SELECT Shape, COUNT(*) / (SUM(COUNT(*)) OVER() * 1.0) AS PercentShape
FROM Commodities GROUP BY Shape

-- OR

SELECT Shape, (COUNT(*) * 1.0) / SUM(COUNT(*)) OVER() AS PercentShape
FROM Commodities GROUP BY Shape

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 21:28:27

everything is not correct ....
sum(count(*)) will not work too...
this was one of the sample questions for 70-433...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-24 : 16:43:09

Yeah, weird.. You can refer to my response as one way to do it. But, I assume, the point of the question is to understand how to partition using the OVER clause. So, if I was you I'd brush up on the use of the OVER clause if you are unfamiliar or uncomfortable with it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources