Summing a aggregate function

Question

I am trying to sum a total that i have from an aggregate function by using a nested select statement and then i have a group by at the end but I am getting an error that the keyword by is not expected.

Here is the code:

SELECT SUM(TOTAL) as CURTOTAL, SUM(LYTOTAL) as CURLYTOTAL
from
(select 
pf.*, ps.*, st.*, RANK() OVER (ORDER BY JULsaless desc) as TOTRANK,
 JANSALESS + FEBSALESS + MARSALESS + APRSALESS + MAYSALESS + JUNSALESS + JULSALES as TOTAL,
JANLYSAL + FEBLYSAL + MARLYSAL + APRLYSAL + MAYLYSAL + JUNLYSAL + JULLYSAL as LYTOTAL` 
from 
payssfile pf left joinpayssspec ps on pf.str#` = ps.str# and pf.item# = ps.item#  join storefile 
st on
pf.str# = st.str# where(year = 2015 and totaltype = '' and pf.str# =38))` group by pf.str#;`

then when I go to run that i get this message:

The keyword BY was not expected here. A syntax error was detected at keyword BY. The partial list of valid tokens is FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

Not sure what's up with the backquotes. Why are you grouping by `pf.str#` when they're all 38? Are you even using `TOTRANK`? Hopefully the optimizer is smart enough to strip out the stuff you didn't really need. It appear could just do sum the expressions directly `sum(JANLYSAL + ... + JULLYSAL)`. — shawnt00, Nov 23 '15 at 22:01
I am using this call to dynamically build a table in c# and the totrank is used with something else. Everything that it getting pulled from this statement is being used in the table — nminar, Nov 23 '15 at 22:04

score 2 · Accepted Answer · edited May 23 '17 at 11:51

Whatever your DBMS is, you are missing an alias. You should always assign an alias for the derived table you're using.

Example of a derived table

select...
from (
-- this part of the query is a derived table:
select ... from ...
) as <ALIAS_HERE> -- must give derived table an alias

This is why you have an error near GROUP BY

In your particular example just replace

)) group by pf.str#;

with

)) foo group by pf.str#; 
-- here you will name your derived table foo, to be able to call it's columns by foo.column_name

Here's a reference to a similar question on SO.

score 0 · Answer 2 · answered Nov 23 '15 at 21:47

0

it looks like you haven't given your sub-query the alias pf, pf is the name of the table within the sub-query, but not sub-query itself. As such, the group by function is broken.

answered Nov 23 '15 at 21:47

Vangrat

1
3

Summing a aggregate function

2 Answers2