Advertisement
Advertisement


SQL Server query - 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, and 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 [email protected]_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.

2019/03/13
1
435
3/13/2019 2:11:55 PM

Accepted Answer

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 [email protected]_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.

2019/02/22
743
2/22/2019 11:55:19 AM

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
2017/07/31

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.

2019/07/02

SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type] 
FROM cm_production 
WHERE [email protected]_number 
GROUP BY program_type
2009/10/05

try this:

SELECT
    COUNT(program_name) AS [Count],program_type AS [Type]
    FROM (SELECT DISTINCT program_name,program_type
              FROM cm_production 
              WHERE [email protected]_number
         ) dt
    GROUP BY program_type
2019/03/13

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)
2017/02/01

Source: https://stackoverflow.com/questions/1521605
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]