Is there a better way to do this ?

[KavJack]

Is there a better way to do this ?
My SQL query
SELECT CUPALL2.YR, CUPALL2.RD, Count(CUPALL2.YR) AS [Count]
FROM CUPALL2
WHERE RP="r"
GROUP BY CUPALL2.YR, CUPALL2.RD
ORDER BY CUPALL2.YR, Iif(CUPALL2.RD="F","Z", CUPALL2.RD )
UNION SELECT CUPALL2.YR, "Z - ALL",Count(CUPALL2.YR) AS [Count]
FROM CUPALL2
WHERE RP="r"
GROUP BY CUPALL2.YR; works but I want to output the order so that the total appears at the bottom for each year. The output looks like this:
Year Round Count
1979 R1 12
1979 R2 8
1979 Z - All 20
1980 R1 11
1980 R2 13
1980 SF 9
1980 Z- All 33
but I have to use the contrivance "Z - ALL" to get my total to appear at the end of each year. Is there about a better way of doing this without having to use the "Z -" in front of the word ALL ?

 

[scriverb]

In your first Select use a Switch statement to create a new column called Sort. It is this column that you will use to properly OrderBy. It will never need to be displayed but the sort number will put your records in the proper order. This way the "Z - All" can be displayed as "All". The red code displays the changes.

Switch(Expression-1, Value1, Expression-2, Value2, Expression-3, Value3, . . . n expressions)

SELECT CUPALL2.YR, CUPALL2.RD, Count(CUPALL2.YR) AS [Count], Switch(CUPALL2.RD = "R1", 1, CUPALL2.RD = "R2", 2, CUPALL2.RD = "SF", 3) AS SORT
FROM CUPALL2
WHERE RP="r"
GROUP BY CUPALL2.YR, CUPALL2.RD
ORDER BY CUPALL2.YR, SORT
UNION SELECT CUPALL2.YR, "ALL",Count(CUPALL2.YR) AS [Count],4 AS SORT
FROM CUPALL2
WHERE RP="r"
GROUP BY CUPALL2.YR;

I think this will give you what you want. If I am off base here please get back and we can figure this out.

Bob Scriver

 

[KavJack]

Thanks for your help. I had to tweak it a bit. I don't think that my first ORDER statement should have been where I had it.
My final code ended up like this :
SELECT CUPALL2.YR, CUPALL2.RD, Count(CUPALL2.YR) AS [Count],
Switch(CUPALL2.RD = "EP", 11, CUPALL2.RD = "PR",12,
LEFT(CUPALL2.RD,1)="Q",20+VAL(RIGHT(CUPALL2.RD,1)),
CUPALL2.RD = "IR", 31,
LEFT(CUPALL2.RD,1)="R",40+VAL(RIGHT(CUPALL2.RD,1)),
CUPALL2.RD = "SF", 50,
CUPALL2.RD = "F", 60) AS SORTX
FROM CUPALL2
WHERE RP="r"
GROUP BY CUPALL2.YR, CUPALL2.RD
UNION SELECT CUPALL2.YR, "ALL" As RD,Count(CUPALL2.YR)
AS [Count], 99 AS SORTX
FROM CUPALL2
WHERE RP="r"
GROUP BY CUPALL2.YR
ORDER BY CUPALL2.YR, SORTX;
Yours etc. Barry Jackson