Click here to Skip to main content
15,166,358 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Well my table result is like this
C#
id  Col1 col2 
 1   2     6
 2   3     6
 3   1     6


now i want the result for the col2 as sum(col1) values and this will be displayed in the col2

so for this i used the query as

SQL
select xx.id , xx.col1  , xx.sum(col1) col2   from 
( select a.id , cout(*) col1 from 
table1 a join table2 b on a.id = b.id ) xx 


on doing so i m getting repeating sum for the col1 now i want the sum in first row and for rest of coloumn
it resturn null value

C#
id  Col1 col2 
 1   2     6
 2   3     0
 3   1     0
Posted
Updated 8-Aug-13 7:45am
v3

Having some table definitions would help, but I believe this is what you want.

SQL
select
     id
    ,col1
    ,case
          when RowId = 1 then SUM(col1) OVER()
          else 0
     end as col2
from (
       select
            id
           ,COUNT(*) AS Col1
           ,ROW_NUMBER() OVER(order by id) as RowId
       from Table1
       group by id) AS xx
   
v2
Comments
lalitkr 8-Aug-13 13:08pm
   
No i want for the first row the sum of the col1 and for the other rows shows the null or zero value as of now it is showing the repeating valur for the other rows also
virusstorm 8-Aug-13 13:44pm
   
I updated my solution. If that doesn't work, I would need to see table definitions to write the query to do what you are looking for.
Maciej Los 10-Aug-13 9:35am
   
See my answer ;)
I'm not sure what do you want to achieve, but have a look at example:
SQL
DECLARE @tmp TABLE (id INT IDENTITY(1,1), Col1 INT, Col2 INT)

INSERT INTO @tmp (Col1)
SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 1
UNION ALL SELECT 6
UNION ALL SELECT 9
UNION ALL SELECT 8
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 7

--select statement
SELECT id,  Col1, Col2 = (SELECT SUM(Col1) AS Col2 FROM @tmp)
FROM @tmp

--update statement
UPDATE @tmp
    SET Col2 = (SELECT SUM(Col1) AS Col2 FROM @tmp)

--display updated values
SELECT id,  Col1, Col2
FROM @tmp
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900