CODEWITHSUNDEEP
sqlsql-servert-sql
FrenkyB
FrenkyB

Reputation: 7197

Count number of rows in subquery

I would like to count number of rows in this subquery:

select t.*
FROM(
select 1 as first, 2 as second
union
select 1 as first, 3 as second
) t

so expected result is:

+-------+--------+-------+
| first | second | count |
+-------+--------+-------+
|     1 |      2 |     2 |
|     1 |      3 |     2 |
+-------+--------+-------+

What I've already tried is to include count(*) into query, but it doesn't work. Any idea on this problem?

Upvotes: 0

Views: 397

Answers (3)

Magnus
Magnus

Reputation: 46909

Another option would be to use a CTE.

with cte as
(
    select 1 as first, 2 as second
    union
    select 1 as first, 3 as second
)
select 
    cte.*,
    (select count(*) from cte) as [count]
from cte

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81930

You can use the window function.

The window functions can be invaluable, and well worth your time to get comfortable with them.

Example

select t.*
      ,count = sum(1) over ()
FROM(
select 1 as first, 2 as second
union
select 1 as first, 3 as second
) t

Returns

first   second  count
1       2       2
1       3       2

Upvotes: 4

Ajay2707
Ajay2707

Reputation: 5798

you can try

select t.*, count(*) over (order by first  ) count
FROM(
select 1 as first, 1 as second
union
select 1 as first, 2 as second
union
select 1 as first, 3 as second
union
select 2 as first, 4 as second
) t

or 

select distinct t.first, count(*) over (order by first  ) count
FROM(
select 1 as first, 1 as second
union
select 1 as first, 2 as second
union
select 1 as first, 3 as second
union
select 2 as first, 4 as second
) t

Upvotes: 1

Related Questions