CODEWITHSUNDEEP
sql-serverselectcountcombinations
radio
radio

Reputation: 1090

sql server select into variable and count result

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

Upvotes: 0

Views: 5175

Answers (5)

Todd Li
Todd Li

Reputation: 3269

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>...

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415860

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.

Upvotes: 0

Bort
Bort

Reputation: 7618

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.

Upvotes: 0

Alex K.
Alex K.

Reputation: 175816

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.

Upvotes: 2

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

try this :

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

Upvotes: 2

Related Questions