Selecting multiple hard coded rows in with subselect

Question

How do I simply hard code multiple rows in a subselect?

I know I could do (How to select several hardcoded SQL rows?):

SELECT x.id, SUM(ISNULL(OtherTable.count_column,0)) 
FROM (SELECT 12 AS id
   UNION
SELECT 21 AS id
   UNION
SELECT 101 AS id
/*AND so on */
) AS x
LEFT JOIN OtherTable ON x.id = OtherTable.id
Group BY x.id

Is there a less awkward and verbose way to do this?

What I am really looking for is:

SELECT id, SUM(ISNULL(count_column,0)) FROM OtherTable
WHERE id IN (12, 21, 101,/*And So On*/)
GROUP BY id

In this case it does not include the sum of 0 for ids that do not exist. Is there a way to include the id's that were not found?

I noticed PIVOT for SQL Server, but I am not sure if that makes it simpler/less verbose.

I guess I am just asking is there a better way?

score 0 · Answer 1 · edited Feb 08 '14 at 23:00

Try something like this using master..spt_values table.

SELECT x.id, SUM(ISNULL(OtherTable.count_column,0)) 
FROM 
(
SELECT DISTINCT number AS id
FROM master..spt_values
WHERE number >= 1 and number <= 10
) AS x
LEFT JOIN OtherTable ON x.id = OtherTable.id
Group BY x.id

OR

SELECT id, SUM(ISNULL(count_column,0)) 
FROM OtherTable
WHERE id IN (
               SELECT DISTINCT number AS id
               FROM master..spt_values
               WHERE number >= 1 and number <= 10
             )
GROUP BY id

Test Data

DECLARE @OtherTable TABLE(ID INT, count_column INT)
INSERT INTO @OtherTable VALUES
(1, 10), (2,20),(3,30),(4,NULL),(5,50)

Query

SELECT x.id, SUM(ISNULL(t.count_column,0)) Total_Sum
FROM 
(
SELECT DISTINCT number AS id
FROM master..spt_values
WHERE number >= 1 and number <= 7
) AS x
LEFT JOIN @OtherTable t ON x.id = t.id
Group BY x.id

Result Set

╔════╦═══════════╗
║ id ║ Total_Sum ║
╠════╬═══════════╣
║  1 ║        10 ║
║  2 ║        20 ║
║  3 ║        30 ║
║  4 ║         0 ║
║  5 ║        50 ║
║  6 ║         0 ║
║  7 ║         0 ║
╚════╩═══════════╝

I should have been clearer, those were just ID's not a range. I will edit question. — rileymat, Feb 08 '14 at 22:45

score 0 · Answer 2 · answered Feb 09 '14 at 03:57

Just declare a temp table which store your ids u used in "IN Clause".

DECLARE @temp TABLE(ID INT identity(1,1), yourIDs INT)
INSERT INTO @temp VALUES
(10),(20),(300),(400)

Do right join to temp table to retrieve sum of count_column against all id's you want

  select t.yourIDs, Sum(isnull(ot.count_column,0)) 
  from OtherTable ot  
  right JOIN  @temp t on t.yourIDs=ot.id
  group by t.yourIDs

Selecting multiple hard coded rows in with subselect

2 Answers2