Count all the DISTINCT program names by program type and push number

SELECT COUNT(DISTINCT program_name) AS Count,
  program_type AS [Type]
FROM cm_production
WHERE push_number = @push_number
GROUP BY program_type

DISTINCT COUNT(*) will return a row for each unique count. What you want is COUNT(DISTINCT <expression>): evaluates expression for each row in a group and returns the number of unique, non-null values.

Function in SQL Server-

     A function is a set of code that perform specific task. There are some predefined function in SQL Server are as follow-

COUNT(), AVG(), SUM(), MAX(), MIN().

SQL COUNT(), AVG() and SUM() functions –

COUNT() –

The SQL COUNT () function is used to find the number of rows in a column of a table.

Syntax-

                        SELECT COUNT(column_ name) FROM table_name WHERE condition;

AVG() function-

The SQL AVG() function is used to find the Average of numerical column values of a table.

Syntax-

                        SELECT AVG(column_name) FROM table_name WHERE condition;

SUM() function –

 The SQL SUM() function is used to find the sum of numerical column values in a table.

Syntax-

                        SELECT SUM(column_name) FROM table_name WHERE condition;

            We have a database table “TeacherDetails”-

Example-

COUNT()-     The following statement is to find the number of row in a column of ‘TeacherDetails’ table.

                                      select count(stuCourse) from TeacherData;

AVG()-        The following statement is to find the Average of numerical column values of ‘TeacherDetails’ table.

                                           select avg(Salary) from TeacherData;

SUM()-     The following statement is to find the sum of the numerical column values in table ‘TeacherDetails’.

                                       select sum(Salary) from TeacherData;

  SQL MIN and MAX –

The SQL function MIN() and MAX() is to find the minimum and maximum values form database table.

The MIN() function is used to find the minimum value from table.

The MAX() function is used to find the maximum value from table.

MIN( )      Syntax-

                            SELECT MIN(column_name) FROM table_name WHERE condition;

MAX() Syntax-

                             SELECT MAX(column_name) FROM table_name WHERE condition; MAX() function

Example-

-                   Let have a table ‘TeacherDetails’.

MAX()-    Follow the statement to find the maximum value from ‘TeacherDetails’ table.

                        select max(Salary) from TeacherData;

To find the record which has maximum Salary then you can use the following statement.

                    select * from TeacherData where Salary=(select max(Salary) from TeacherData);

MIN()-        The following statement is used to find the minimum Salary from ‘TeacherDetails’ table.

                               select min(Salary) from TeacherData;

To find the record of that row which has minimum value is like as-

                   select * from TeacherData where Salary =(select min(Salary) from TeacherData);