SQL Server query - Selecting COUNT(*) with DISTINCT

Question

SQL Server query - Selecting COUNT(*) with DISTINCT

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 push_number=@push_number 

GROUP BY program_type

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

sql sql-server

3 years ago by Amit8z4mc

3 Answers

Login / Signup to Answer the Question.

Login Signup

**espadacoder11** · Answer 1 · 2021-06-08T12:22:53

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.

**kshitijrana14** · Answer 2 · 2021-06-10T04:42:34

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.

**RoliMishra** · Answer 3 · 2021-06-14T13:49:14

SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type] 

FROM cm_production 

WHERE push_number=@push_number 

GROUP BY program_type

C TUTORIAL

C PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

C++ TUTORIAL

C++ PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

PYTHON TUTORIAL

PYTHON HOW TOS

INTERVIEW TESTS

EXECUTE CODE

JAVA TUTORIAL

JAVA CODE EXAMPLES

SPRING TUTORIAL

MORE IN JAVA

COMPUTER ARCHITECTURE

COMPUTER NETWORK

OPERATING SYSTEM

DBMS & SQL

PL/SQL

MongoDB

EXECUTE SQL

ANDROID DEVELOPMENT

GO LANGUAGE

LINUX

DOCKER

HTML TAGS (A to Z)

CSS REFERENCES

SASS/SCSS

KOTLIN

GAME DEVELOPMENT

PHP

GIT GUIDE

JAVASCRIPT

ADVANCED DSA

SQL Server query - Selecting COUNT(*) with DISTINCT

3 Answers

Login / Signup to Answer the Question.