Selecting COUNT(*) with DISTINCT – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server, sql-server-2005. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
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.
Solution :
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
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)
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.