Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-07-13 : 13:19:07

Here is my source table:
ID C_NO L_VALUE B_VALUE
1 1 200 300
1 2 500 100
1 1 200 300

And My end should be like this
ID L_VALUE B_VALUE
1 700 400
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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-13 : 13:24:07

SELECT ID, SUM(L_VALUE) L_VALUE, SUM(B_VALUE) B_VALUE
FROM myTable
GROUP BY ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-13 : 13:24:12

You need to alias your derived table. I used t:

Select SUM(L_VALUE), SUM(B_VALUE) FROM (SELECT DISTINCT ID,C_NO FROM MYTABLE GROUP BY ID,C_NO) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-13 : 13:25:15

And I agree that Rob's solution is the right solution, but I wanted to ensure the OP knew why the error occurred.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-07-13 : 14:00:15

Thank you guys for reply, i tried both query that you suggest, tkizer query giving me error and robvolk query run fine but end result not what i want, here is my tweak

-- FIRST STEP

SELECT DISTINCT C_NO,ID,L_VALUE,B_VALUE INTO NEW_TABLE FROM MYTABLE
GROUP BY IDE,C_NO,L_VALUE,B_VALUE

-- SECOND STEP

SELECT ID,SUM(L_VALUE) L_VALUE,SUM(B_VALUE) B_VALUE INTO NEW_TABLE1 FROM NEW_TABLE
GROUP BY ID

Please guide me if i can use step1 and step2 in one single statement, Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-13 : 14:16:58

I think you want:

SELECT ID, SUM(DISTINCT L_VALUE) L_VALUE, SUM(DISTINCT B_VALUE) B_VALUE
FROM myTable
GROUP BY ID

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-07-13 : 14:29:04

Thanks its works fine, awesome, just for queresty
if the data is like this

ID C_NO L_VALUE B_VALUE
1 1 200 300
1 2 200 100
1 1 200 300

and my end result should be

ID L_VALUE B_VALUE
1 400 400

How i can handel because my C-NO is different

SELECT ID, DISTINCT C_NO,SUM(DISTINCT L_VALUE) L_VALUE,SUM(DISTINCT B_VALUE) B_VALUE
FROM MYTABLE
GROUP BY ID

And i am getting error, Could you please guide me where i am wrong. Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-13 : 14:41:48

You can't include C_NO in the SELECT list unless you also add it to the GROUP BY clause. It's also usually pointless to use DISTINCT with GROUP BY, as GROUP BY will remove duplicates as DISTINCT does.

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-07-13 : 14:54:55

SELECT ID,SUM(DISTINCT L_VALUE) L_VALUE,SUM(DISTINCT B_VALUE) B_VALUE
FROM MYTABLE
GROUP BY ID,C_NO

I am getting this result
ID L_VALUE B_VALUE
1 200 300
1 200 100

But it should be
ID L_VALUE B_VALUE
1 400 400

Am i missing something?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-13 : 15:01:18

;WITH a(ID, C_NO, L_VALUE, B_VALUE) AS (SELECT DISTINCT ID, C_NO, L_VALUE, B_VALUE FROM myTable)
SELECT ID, SUM(L_VALUE) L_VALUE, SUM(B_VALUE) B_VALUE FROM a

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-07-13 : 15:06:25

AWESOME WORK FINE, Just want to give you update, made minnor change to run this query

;WITH a(ID, C_NO, L_VALUE, B_VALUE) AS (SELECT DISTINCT ID, C_NO, L_VALUE, B_VALUE FROM myTable)
SELECT ID, SUM(L_VALUE) L_VALUE, SUM(B_VALUE) B_VALUE FROM a
GROUP BY A.PIN

Thnaks for help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources