sql server select into variable and count result

0
votes

Something very simple, but I cannot get it working for me :)

select x.name, count(x.name) from <table_name> x where ...<complex and long>...

It throws an error, that the x.name isn't used with group by.

select x.name from <table_name> x where ...<complex and long>...

works fine and returns e.g. 6 names

select count(x.name) from <table_name> x where ...<complex and long>...

works also fine and returns e.g. the number 6

But the combination is not working, when I tried to add the group by:

select x.name, count(x.name) from <table_name> x where ...<complex and long>...
group by x.name

it worked, but all counts were 1 and not 6.

The thing is, I could firstly get the count into variable, then write the long sql statement just to get the names, but I don't want to write the long complex select statement twice. There has to be some way to do the combination in one select: get all names and by the way tell me how many they were.

Thanks

P.S.

name    bla1    bla2
a       ...     ...     
a       foo     ...     
b       foo     ...     
c       ...     ...     
d       foo     ...     
d       foo     ...     
b       foo     ...     
c       foo     ...

The result of x.name where bla1 = foo would be:

a
b
d
d
b
c

The result of count(x.name) where bla1 = foo would be:

6

My desired result of:

...variable definitions
select @foundName = x.name, @numOfAllFoundNames = count(x.name)
from <table_name> x where ...<complex and long>...

should be: @foundName = a (just one of the names, no matter which one) @numOfAllFoundNames = 6

5
sql-serverselectcountcombinations
plz share some sample table content to explain the way it works. COUNT is an aggregate function and it expects either grouping or applied on complete table. What is the output you are expecting really? – sundar

5 Answers

2
votes

The simplest way is to use @@rowcount after the query;

select x.name from <table_name> x where ...<complex and long>...

select 'the number of rows is:', @@rowcount

As an aside, if you request a non-aggregate field (name) and an aggregated field (count(name)) you must provide a group by to tell the server what to count; you see 1's because group by name applies the count to every distinct name in the set - E.g. you would see 1 less row and 2 if a name was repeated.

2
votes

try this :

select x.name, count(x.name) over () cnt 
from <table_name> x where ...<complex and long>...
0
votes

You could use @@ROWCOUNT after selecting the names to get the number of rows returned by your complicated query. Otherwise, there's no simple way to get the number of names in the same query that selects each name.

0
votes

This is a job for the little-used COMPUTE clause:

SELECT x.name
FROM [table]
WHERE [... ]
COMPUTE COUNT(x.name)

Just be wary that COMPUTE has been deprecated.

0
votes

You almost got it right.

...variable definitions
set @numOfAllFoundNames = 0;
select @foundName = x.name, @numOfAllFoundNames = @numOfAllFoundNames+1
from <table_name> x where ...<complex and long>...