July 13, 2011 at 11:20 am
Here is my source table:
IDC_NOL_VALUE B_VALUE
11200300
12500100
11200300
And My end should be like this
IDL_VALUE B_VALUE
1700400
Here is query that I am using
Select SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT ID,C_NO FROM MYTABLE GROUP BY ID,C_NO)
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
AND here is the error message that I am getting. Please guide me where I am wrong. Thanks for help.
July 13, 2011 at 11:23 am
Try this instead
Select SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT L_VALUE,B_VALUE FROM MYTABLE )
but I think this is what you want
Select ID, SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT ID,L_VALUE,B_VALUE FROM MYTABLE )GROUP BY ID
July 13, 2011 at 2:13 pm
Ignoring any potential logic problems, the syntax error is caused by not naming the derived table, like so
Select SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT ID,C_NO FROM MYTABLE GROUP BY ID,C_NO) X
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 13, 2011 at 6:54 pm
Ignoring any potential logic problems, the syntax error is caused by not naming the derived table, like so
Select SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT ID,C_NO FROM MYTABLE GROUP BY ID,C_NO) X
Tad,
Actually if you paid attention the subquery has different columns from the sum that is the main issue.
July 14, 2011 at 12:30 am
hope this will work.
Select id, SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT ID, L_VALUE , B_VALUE FROM MYTABLE) t GROUP BY ID
July 14, 2011 at 9:49 am
bopeavy (7/13/2011)
Ignoring any potential logic problems, the syntax error is caused by not naming the derived table, like so
Select SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT ID,C_NO FROM MYTABLE GROUP BY ID,C_NO) X
Tad,
Actually if you paid attention the subquery has different columns from the sum that is the main issue.
The main issue is the syntax error caused by not aliasing the derived table which means that neither of the queries you posted will work. The second problem is that his columns on the outside do not match the columns on the inside.
How appropriate you talk about paying attention yet get my name wrong. /fail
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply