Selecting COUNT(*) with DISTINCT

Question

In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, program_name and push_number along with some other columns.

GOAL: Count all the DISTINCT program names by program type and push number.

What I have so far is:

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

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

This gets me partway there, but it's counting all the program names, not the distinct ones (which I don't expect it to do in that query). I guess I just can't wrap my head around how to tell it to count only the distinct program names without selecting them. Or something.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

You can try the following query.

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

Answer 7

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.

Answer 8

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)