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.

I needed to get the number of occurrences of each distinct value. The column contained Region info. The simple SQL query I ended up with was:

SELECT Region, count(*)
FROM item
WHERE Region is not null
GROUP BY Region

Which would give me a list like, say:

Region, count
Denmark, 4
Sweden, 1
USA, 10

You have to create a derived table for the distinct columns and then query the count from that table:

SELECT COUNT(*) 
FROM (SELECT DISTINCT column1,column2
      FROM  tablename  
      WHERE condition ) as dt

Here dt is a derived table.

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

try this:

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

You can try the following query.

SELECT column1,COUNT(*) AS Count
FROM tablename where createddate >= '2022-07-01'::date group by column1

To count distinct program names by program type and push number in SQL Server 2005, you can use a subquery to first select the distinct program names, and then perform the count in the outer query. Here's an example query that should give you the desired result:

DECLARE @push_number INT;
SET @push_number = [HERE_ADD_NUMBER];

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

In this query, the subquery selects the distinct program names and program types from the cm_production table based on the specified push_number. Then, in the outer query, you perform the count on the distinct program names and group the results by program type.

By the way, with dbForge Studio for SQL Server , you can easily write, execute, and analyze your SQL queries.

This is a good example where you want to get count of Pincode which stored in the last of address field

SELECT DISTINCT
    RIGHT (address, 6),
    count(*) AS count
FROM
    datafile
WHERE
    address IS NOT NULL
GROUP BY
    RIGHT (address, 6)