Looping through Distinct values in SQL, and using the distinct values in subquery

Question

I am a bit new to SQL so I would appreciate some help with this.

Say I have a table that looks something like the following:

| Manufacturer | ProductName | Price |
|--------------|-------------|-------|
| A            | p1          | 1.00  |
| A            | p2          | 1.50  |
| B            | p3          | 1.00  |
| C            | p4          | 3.50  |
| C            | p5          | 3.50  |
| C            | p6          | 6.00  |

How do I loop through the table to return Each manufacturer, the number of products they sell and the average price of the products they sell. I can do each of these steps in a separate query but I am not sure how to put them all together in a loop.

For example, I know that "SELECT DISTINCT Manufacturer from table" will give me a list of all the manufacturers, but then how do I use each of these manufacturers to COUNT the products and AVG the prices? For Example, I would like to return:

Manufacturer   NumberofProducts        AveragePrice
A                  2                     1.25
.
.
.

Answer 1

Use aggregated function with group by

select Manufacturer, count(productname) as countofProduct, avg(price) as avgprice
from tablename
group by Manufacturer