Selecting Count(*) With Distinct

Selecting COUNT(*) with DISTINCT

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.

Why doesn't COUNT(DISTINCT (*)) work?

The truth of the matter is that SQL (Server) or any other SQL implementation is not supposed to do everything under the sun.

There are reasons to limit the SQL syntax to certain elements, from the parsing layer to query optimization to predictability of results to just common sense.

The COUNT aggregate function is normally implemented as a streaming aggregate with a gate for a single item, be it * (record count, just use a static token), or colname (increment token only when not null) or distinct colname (a hash/bucket with one key).

When you ask for COUNT(DISTINCT *) or for that matter, COUNT(DISTINCT a,b,c) - yes, it can surely be done for you if some RDBMS sees fit to implement it one day; but it is (1) uncommon enough (2) adds work to the parser (3) adds complexity to the COUNT implementation.

Mark has the correct alternative.

How to count distinct group by field SQL

Use this

select nama,count(distinct tanggal) from your_table
group by nama;

http://rextester.com/ZOZQK78377

SQL count(*) and distinct

select count(*) from (select distinct * from MyTable) as T

Although I strongly suggest that you re-think any queries that use DISTINCT. In a large percentage of cases, GROUP BY is more appropriate (and faster).

EDIT: Having read the question comments, I should point out that you should never ask the DBMS to do more work than actually needs doing to get a result. If you know in advance that there will not be any duplicated rows in a table, then don't use DISTINCT.

What's the difference between select distinct count, and select count distinct?

Query select count(distinct a) will give you number of unique values in a.

While query select distinct count(a) will give you list of unique counts of values in a. Without grouping it will be just one line with total count.

See following example

create table t(a int)

insert into t values (1),(2),(3),(3)


select count (distinct a) from t

select distinct count (a) from t
group by a

It will give you 3 for first query and values 1 and 2 for second query.

Get conditional count and conditional DISTINCT count in a single SELECT

Use the aggregate FILTER clause. Then you can combine your count with DISTINCT:

SELECT s.logged_on::date AS login_date
     , count(*)                FILTER (WHERE s.device = 'mobile') AS mobile_count
     , count(DISTINCT user_id) FILTER (WHERE s.device = 'web') AS web_count
FROM   session_log s
JOIN   standard_users su USING (user_id)
GROUP  BY login_date;

See:

• Aggregate columns with additional (distinct) filters

I also simplified your twisted formulation with LEFT JOIN and then IS NOT NULL. Boils down to a plain JOIN.

If referential integrity between session_log.user_id and standard_users.user_id is enforced with a FK constraint, and standard_users.user_id is defined UNIQUE or PK - as seems reasonable - you can drop the JOIN completely:

SELECT logged_on::date AS login_date
     , count(*)                FILTER (WHERE device = 'mobile') AS mobile_count
     , count(DISTINCT user_id) FILTER (WHERE device = 'web') AS web_count
FROM   session_log
GROUP  BY 1;

SQL count all distinct and sub totals

You can calculate your total distinct companies separately and then join it to a query that returns the per scope totals.

select 
    scopeID,
    count(distinct companyID) total_distinct_companies_of_each_scope,
    total.total_companies
from 
    tmp
join 
    (select count(distinct companyID) total_companies from tmp) total
group by scopeID

Returns

---