By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,209 Members | 1,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,209 IT Pros & Developers. It's quick & easy.

Ranking in Crosstab Query

P: 10
Hi,

I have a crosstab query which is based on a simple select query. Here is how the query works..

The base query gets it date from employee table and their incentives table for the given quarter. This query feeds to the Crosstab query for displaying the quarterly data. I have a field in the crosstab query which calculates the sum of all three months in the quarter.

I want to give ranks to this quarterly total in the crosstab query. I tried using the rank function posted by someone on this forum with the subquery. But subquery doesnt seem to be working in the crosstab query.

Can anybody help.. its urget...

Thanks

Keyur
Jan 7 '08 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Keyur

Please post the SQL of your crosstab query.

Also explain how you want the data ranked.

Mary
Jan 8 '08 #2

P: 10
Hi Mary,

For the confidentiality reasons I can not give you the exact sql of my crosstab. However below given is the same syntax I am using except the query and column names.

TRANSFORM Sum(Query1.Coiumn1) AS QuarterlyTotal
SELECT Query.Column2, Query1.Column3, Query1.Column4, Query1.Column5, Query1.Column6, Sum(Query1.Column1) AS NetIncentives
FROM Query1
GROUP BY Query1.Column2, Query1.Column3, Query1.Column4, Query1.Column5
PIVOT Query1.Column6;

I have a query that fetches data of the incentives earned by each employee from the table in the vertical format for a given quarter, say it Query1.

I am then using Query1 to make a crosstab query, say Query2, in which I am showing incentives earned by each employee in monthly basis (i.e. horizontal format) and I am showing the total for the quarter in the last column. I want to give ranks to employees on this Quarterly total.

Looking forward for your quick reply..

Thanks

Keyur
Jan 9 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Keyur

What kind of Ranks? Just 1, 2, 3, 4 ....

Or do you want to rank them in ranges like

High (>10,000)
Medium Between (1,000 and 9,999)
Low (< 1,000)

What kind of ranking are we talking about?
Jan 9 '08 #4

P: 10
Keyur

What kind of Ranks? Just 1, 2, 3, 4 ....

Or do you want to rank them in ranges like

High (>10,000)
Medium Between (1,000 and 9,999)
Low (< 1,000)

What kind of ranking are we talking about?
Hi,

Ya just simple ranks like 1,2,3,4 ....

I want to show the top incentives earners in the particular quarter

thanks

Keyur
Jan 10 '08 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Query1.Coiumn1) AS QuarterlyTotal
  2. SELECT Query.Column2, Query1.Column3, Query1.Column4, Query1.Column5, Query1.Column6, Sum(Query1.Column1) AS NetIncentives
  3. FROM Query1
  4. GROUP BY Query1.Column2, Query1.Column3, Query1.Column4, Query1.Column5
  5. ORDER BY Sum(Query1.Column1) DESC
  6. PIVOT Query1.Column6;
  7.  
Will the Order by give you what you want ?
Jan 11 '08 #6

P: 10
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Query1.Coiumn1) AS QuarterlyTotal
  2. SELECT Query.Column2, Query1.Column3, Query1.Column4, Query1.Column5, Query1.Column6, Sum(Query1.Column1) AS NetIncentives
  3. FROM Query1
  4. GROUP BY Query1.Column2, Query1.Column3, Query1.Column4, Query1.Column5
  5. ORDER BY Sum(Query1.Column1) DESC
  6. PIVOT Query1.Column6;
  7.  
Will the Order by give you what you want ?
Hi,

Sorry for the late reply as was on leave for 4 days.

I tried your suggestion, it gives following error.


"Cannot have aggregare function in ORDER BY clause (sum(Query1.Column1))"

Regards

Keyur
Jan 15 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.