Need help for a query with COUNT(*) that need to 0

Andre Couture 21

Hello,

I need to query a table and display a line if an item is found or not.

Here is the query

SELECT NAME, COUNT(*)
FROM TABLERA
WHERE NAME IN ('xxx', 'yyy', 'aaa', 'bbb', 'vvv')
GROUP BY NAME;

What I need is a response that looks a bit like that

xxx 0
yyy 1
aaa 1
bbb 1
vvv 0

So far all I tried only return the row for which there's a match.

CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-02-01T02:56:20.04+00:00

Hi @Andre Couture ,

Any update for this thread?

Accepted answer

Martin Cairney 2,261 Reputation points

2021-01-28T00:06:13.003+00:00
Your answer suggests that some of the values do not exist in the table - so this approach will never be able to generate the rows.

One method could be to create a temp table with the values you want and then
LEFT JOIN
this to the main table:

SELECT b.NAME, COUNT(a.NAME) FROM (VALUES ('xxx'),('yyy'),('aaa'),('bbb'),('vvv')) as b(NAME) LEFT JOIN TABLERA a ON a.NAME = b.NAME GROUP BY b.NAME
Please sign in to rate this answer.
Andre Couture 21 Reputation points

2021-01-28T01:02:52.743+00:00

Thanks, that really help. but still have questions

I'm doing this as part of a JMeter JDBC Request which, I believe, has some limitations.

Is there such things as TEMP (Ephemeral) tables in MSQL that could be created on the spot from a list of values as 'xxx','yyy','aaa','vvv' which is what I receive as argument?

Can all this be put in a single script?

Dan Guzman 9,401 Reputation points

2021-01-28T03:07:43.36+00:00

You can create a temp table from data returned by the table value constructor in @Andre Couture 's code:

SELECT NAME INTO #ephemoral FROM (VALUES ('xxx'),('yyy'),('aaa'),('bbb'),('vvv')) as b(NAME); SELECT b.NAME, COUNT(*) FROM #ephemoral AS b LEFT JOIN TABLERA AS a ON a.NAME = b.NAME GROUP BY b.NAME; SELECT b.NAME, COUNT(*) FROM #ephemoral AS b LEFT JOIN TABLERC AS c ON c.NAME = b.NAME GROUP BY b.NAME;

Note the lifetime of a local temp table (single '#' prefix) is the connection. The table will be dropped when the connection is closed unless explicitly dropped beforehand. It cannot be used by other connections.

Martin Cairney 2,261 Reputation points

2021-01-28T04:51:25.123+00:00

What version of SQL Server are you using? Do you have the ability to create a stored procedure?

There are other ways to do this depending upon the restrictions of your instance and database

Andre Couture 21 Reputation points

2021-02-01T03:01:59.123+00:00

not sure what version but sure have very limited access...
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

0 additional answers

Your answer