CODEWITHSUNDEEP
sqlsql-server-2008count
Fearghal
Fearghal

Reputation: 11427

SQL Sever: sum col in one table based on values in another

How can i Sum values in one table only if they exist in another.

eg table1 - typeOfBiscuits

biscuits
jaffacakes
digestives

table2 - inventry
type number
digestives 2
digestives 3
jaffacakes 2
digestives 3
jaffacakes 4
cheesecake 1

Desired result of select Count(number) from Inventry where type = <not sure how to get type from typeOfBisuit table>

type count
digestives 8
jaffacakes 6

My attempt is as follows but i get a 'reference to multi part string' error

select Count(number) from Inventry where type = typeOfBiscuits.type

Upvotes: 1

Views: 227

Answers (3)

Gidil
Gidil

Reputation: 4137

Try this:

SELECT TYPE, 
       Sum(NUMBER) AS [Count] 
FROM   TABLE2 
WHERE  TYPE IN (SELECT TYPEOFBISCUITS 
                FROM   TABLE1) 
GROUP  BY TYPE

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33474

SELECT typeOfBiscuits.biscuits, Sum(Inventory.Number) as [Count]
FROM typeOfBiscuits INNER JOIN Inventory
ON typeOfBiscuits.biscuits = Inventory.type
GROUP BY typeOfBiscuits.type

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try:

SELECT 
 t1.Type,
 SUM(number) as [Count]
FROM
 inventry t1 INNER JOIN typeOfBiscuits t2 ON t1.Type=t2.Type
GROUP BY t1.Type

Upvotes: 0

Related Questions