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.
| Author |
Topic |
|
chiptay
Starting Member
1 Post |
 Posted - 2005-09-04 : 21:43:19
|
| Thank you in advance for any suggestions you have.I need to calculate a percentage using the count of rows in subquery “X”. Since the [Percent] calculated in the SELECT statement has a subquery of its own to calculate the denominator, it doesn’t seem to recognize anything related to the X subquery.I need a date range for the transactions, and I’ve put the X.DateTimeStamp as one of the fields in X that I can use in the main query’s WHERE statement. The Begin and End date of the range will actually be filled in at runtime, but the SQL Grid View that I’m using can’t to put the date range into a subquery.How can I get the denominator of the Select [Percent] to be something like “(SELECT count(1) from X)” ?Thanks.ChiptaySELECT isnull(FruitType,'ALL') as FruitType, count(1) as Pieces, avg(X.Weight) as [Avg Weight], [Percent] = convert(decimal(5,1),round((COUNT(*) * 100.0/ (SELECT COUNT(1) FROM basket WHERE ReadyToPick = 1)),2)), COUNT(CASE WHEN lower(X.comment) = ‘sweet' then 1 end) as [sweet], COUNT(CASE WHEN lower(X.comment) = ' tart' then 1 end) as [tart]FROM (SELECT S.*, FAMILY, FruitType, from basket B join specifics S on B.ReadyToPick ID = S.ReadyToPickID where B. ReadyToPick = 1) XWHERE X.DateTimeStamp BETWEEN '08/01/05' and '09/01/05'GROUP BY FruitType WITH ROLLUPORDER BY GROUPING(FruitType), 2 desc |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-09-04 : 23:08:47
|
| Sample data and sample resultset would help... |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-05 : 01:44:56
|
| and also post table structureMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Help! Calculating percentage using a subquery