CODEWITHSUNDEEP
sqlpostgresql
liltitus27
liltitus27

Reputation: 1750

I want `WHERE IN` to return 0 if an `IN` field does not exist

Consider the query:

SELECT id, count(id) AS "count"
FROM table
WHERE id IN (
    'value1',
    'value2',
    'value3'
)
GROUP BY id
ORDER BY count ASC;

If each of those IN values are present in table, then I'd get a result as such:

RESULTS
-------
value1    <value>
value2    <value>
value3    <value>

But if, say, value2 was not present in table, then I'd get

RESULTS
-------
value1    <value>
value3    <value>

Notice the absence on value2 in the results.

I'd like to have value2 displayed with a value of 0.

For context, I have a table of Rep IDs associated with Subscription IDs. And I want to query this to get the Rep with the least amount of Subscriptions. The issue is that the Rep IDs in this table are not exhaustive. So, I programmatically query a separate datasource that contains the exhaustive list of Rep IDs. My idea is to somehow create a query, using that exhaustive list of Rep IDs, to get the desired response I mentioned above.

Does anyone have any ideas for this? I've seen similar postings about this where the solution was to use COALESCE, but my subquery returns more than one result, which caused PostgreSQL to burp.

Upvotes: 2

Views: 111

Answers (2)

user6232480
user6232480

Reputation: 31

You can build up the missing value using below query...test it out. Hope I understand your question correctly. 

DROP TABLE TEST2
GO
CREATE TABLE TEST2(ID INT, M1 INT)
GO
INSERT INTO TEST2 VALUES (1,20)
INSERT INTO TEST2 VALUES (2,25)
INSERT INTO TEST2 VALUES (3,30)
GO
SELECT * FROM TEST2 
GO
SELECT CONVERT(INT,B.N) AS NB, A.M1  FROM TEST2 A RIGHT JOIN
(

SELECT TOP (SELECT MAX(ID) FROM TEST2) n = ROW_NUMBER() OVER (ORDER BY number) FROM [master]..spt_values AS A 
 ) B
 ON A.ID = B.n 

GO
DELETE FROM TEST2 WHERE ID =2
GO

SELECT CONVERT(INT,B.N) AS NB, A.M1  FROM TEST2 A RIGHT JOIN
(

SELECT TOP (SELECT MAX(ID) FROM TEST2) n = ROW_NUMBER() OVER (ORDER BY number) FROM [master]..spt_values AS A 
 ) B

 ON A.ID = B.n

Upvotes: 0

user330315
user330315

Reputation:

You can build up a list that you can outer join to with a values() clause:

SELECT d.id, count(t.id) AS "count"
FROM (
  values 
    ('value1'),
    ('value2'),
    ('value3')
) as data (id)
  left join the_table t on t.id = d.id
GROUP BY d.id
ORDER BY count ASC;

Note that you must use count(t.id) (counting values from the joined table) to get the count of 0

Alternatively you can use a common table expression if you don't want to clutter the from part with the values clause:

with data (id) as (
  values 
    ('value1'),
    ('value2'),
    ('value3')
)
SELECT d.id, count(t.id) AS "count"
FROM data d
  left join the_table t on t.id = d.id
GROUP BY d.id
ORDER BY count ASC;

Upvotes: 5

Related Questions