Adding SUM to SQL Statement 1

[PrgrmsAll]

In QueryAnalyzer I have the following simple statement:

SELECT SUPPLIER.SUPPLIER_ID, PARTS.PARTS_ID
FROM SUPPLIER INNER JOIN
PARTS ON SUPPLIER.SUPPLIER_ID = PARTS.SUPPLIER_ID
GROUP BY PARTS.PARTS_ID, SUPPLIER.SUPPLIER_ID
HAVING (SUPPLIER.SUPPLIER_ID = 1020)


This returns the following recordset:

SUPPLIER.SUPPLIER_ID PARTS.PARTS_ID
1020 02
1020 07
1020 16


What I ultimately wish to achieve is a recordset that would simply give me the SUPPLIER_ID and a sum of the parts. For example, the above supplier has three distinct parts, therefore, I would expect the recordset to look like this:

SUPPLIER.SUPPLIER_ID TOTAL_PARTS
1020 3


Something tells me this is probably fairly easy to achieve yet I seem to be spinning my wheels. Any help would be much appreciated!

Thanks in advance.

 

[ifntech]

change parts.parts_id

to count(parts.parts_id)


Right now it is saying give me any differences in parts_id
which returns 02,07,16

 

[vongrunt]

SELECT SUPPLIER.SUPPLIER_ID, [!]COUNT(PARTS.PARTS_ID) AS TOTAL_PARTS[/!]
FROM  SUPPLIER INNER JOIN
               PARTS ON SUPPLIER.SUPPLIER_ID = PARTS.SUPPLIER_ID
WHERE (SUPPLIER.SUPPLIER_ID = 1020)
GROUP BY SUPPLIER.SUPPLIER_ID [!]--, PARTS.PARTS_ID[/!]

There is no need for HAVING clause...

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[gmmastros]

SELECT SUPPLIER.SUPPLIER_ID, 
       Count(PARTS.PARTS_ID) As Total_Parts
FROM   SUPPLIER 
       INNER JOIN PARTS 
         ON SUPPLIER.SUPPLIER_ID = PARTS.SUPPLIER_ID
GROUP BY SUPPLIER.SUPPLIER_ID 
HAVING (SUPPLIER.SUPPLIER_ID = 1020)

Looks like you don't even need the suppliers table, though (Unless this is part os a bigger sql statement).

SELECT PARTS.SUPPLIER_ID, 
       Count(PARTS.PARTS_ID) As Total_Parts
FROM   PARTS 
WHERE  PARTS.SUPPLIER_ID = 1020
GROUP BY PARTS.SUPPLIER_ID

If you remove the where clause here, you will get a count of all parts grouped by supplier.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

In that case, we can be pretty sure that COUNT(*) is good enough (PARTS_ID is NOT NULLable). Shall we make it even shorter?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[PrgrmsAll]

Thanks to all for your invaluable input. Much appreciated.