2

I need to run a simple query against a purchase invoices table to sum the value of a subset of suppliers invoices. Normally this would be a simple case of the following pseudo query;

Select Sum(invoiceTotal) 
From PurchaseInvoices
Where ContactId = x

The problem that I have is that the particular suppliers that I'm after are randomly distributed throughout the contacts table so I can't simply amend the where clause to say Between x and y as it would draw in records I don't want.

If for argument's sake I need to sum the values of contacts a,b,c,g,j,k,s and u is there a way to define a list of contacts at the outset of a SQL query and then simply restrict the where clause to ContactId's that fall within that list?

halfer
  • 18,701
  • 13
  • 79
  • 158
Dom Sinclair
  • 2,310
  • 1
  • 25
  • 33

1 Answers1

3

You can use IN clause to provide a list or sub-query that returns the list.

Select Sum(invoiceTotal) From PurchaseInvoices
Where ContactId in (x, y, z)

Syntax

test_expression [ NOT ] IN   
( subquery | expression [ ,...n ]  
)
Adil
  • 139,325
  • 23
  • 196
  • 197
  • Thank you for reminding me of the blindingly obvious. I'm clearly too tired. Will mark as correct in a few minutes when the system allows me to. – Dom Sinclair Aug 01 '16 at 06:20
  • You are welcome, no worries sometime we miss the obvious. – Adil Aug 01 '16 at 06:36
  • Just as a matter of interest could this be parameterised and used in a stored procedure (in other word Passing in a list of integers to be referred to in the IN part of the Where clause)? – Dom Sinclair Aug 01 '16 at 06:51
  • Yes, probably this would help, http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure – Adil Aug 01 '16 at 06:56
  • 1
    Thanks, a very useful link. – Dom Sinclair Aug 01 '16 at 07:00