subquery result in sql

Question

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 8-Aug-13 6:31am

lalitkr

Updated 8-Aug-13 6:45am

ridoy

v3

Add a Solution

2 solutions

Add a Solution

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

virusstorm · Answer 1 · 2013-08-08T07:03:00

Solution 1

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

Posted 8-Aug-13 7:03am

virusstorm

Updated 8-Aug-13 7:43am

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 ;)

Maciej Los · Answer 2 · 2013-08-10T03:34:00

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

subquery result in sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

subquery result in sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us