sum a field from sub query

[yfatgo]

Hello, im trying to get the percentage of one field, meaning, to divide the field in the sum of that field.
the thing. the fields are also getting by a query :

select
Query,
Frequency,
Frequency/sum(Frequency) as FreqPerc,
Clicks,
BuyClicks
from (
SELECT top 10 Query,
SUM(Frequency) AS Frequency,
SUM(Clicks) AS Clicks,
SUM(BuyClicks) AS BuyClicks
FROM T_ANLX_REPORT_FRONT_QUERY
where StartTime >= 0 AND EndTime<= 555555555555
GROUP BY Query ) subq
group by subq.Query, subq.Frequency,subq.Clicks, subq.BuyClicks

somehow the Frequency field is equal to the FreqPerc.
what seems to be the problem ?
thanks.

 

[vongrunt]

Aggregate functions (SUM included) don't work that way. Try something like this:

- insert top 10 into temp table, plus empty column FreqPerc
- calculate total sum for temp table.Frequency
- update FreqPerc with Frequency/total sum

 

[SQLSister]

Create anothe derived table with the following code:

SELECT frequency, sum(frequency) from FROM  T_ANLX_REPORT_FRONT_QUERY
where StartTime >= 0  AND EndTime<= 555555555555
GROUP BY Frequency

Then join it to the first one on the Frequency column.


Questions about posting. See faq183-874