September 4, 2005 at 8:12 pm
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.
Chiptay
SELECT 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) X
WHERE X.DateTimeStamp BETWEEN '08/01/05' and '09/01/05'
GROUP BY FruitType WITH ROLLUP
ORDER BY GROUPING(FruitType), 2 desc
September 6, 2005 at 12:55 pm
I have no way of testing this, but I did notice that DateTimeStamp, comment, and Weight are not selected within sub-select X.
I wasn't born stupid - I had to study.
September 9, 2005 at 1:50 am
I expect that you are bringing DateTimeStamp, comment, and Weight from table S. One way to get denominator of the Select [Percent] is by using temporary tables, storing the result set in it and using it all the way in the query, secondly as per my assumption you want to use the result set X in the select count(1) of final resultset [Percent] column.
SELECT
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
(SELECT
S.*,
FAMILY,
FruitType,
from basket B
join specifics S on B.ReadyToPick ID = S.ReadyToPickID
where B. ReadyToPick = 1) X 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) X
WHERE X.DateTimeStamp BETWEEN '08/01/05' and '09/01/05'
GROUP BY FruitType WITH ROLLUP
ORDER BY GROUPING(FruitType), 2 desc
I think that will do.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply