Can I use COUNT() and DISTINCT together?
Sure.
SELECT COUNT(DISTINCT column) FROM table;
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.
Using DISTINCT and COUNT together in a MySQL Query
use
SELECT COUNT(DISTINCT productId) from table_name WHERE keyword='$keyword'
Counting DISTINCT over multiple columns
If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns.
Once it is persisted, provided the column is deterministic and you are using "sane" database settings, it can be indexed and / or statistics can be created on it.
I believe a distinct count of the computed column would be equivalent to your query.
SQL Server how can I use COUNT DISTINCT(*) in HAVING clause?
You probably want SELECT COUNT(DISTINCT orderid)
instead of DISTINCT COUNT(customerid)
:
USE MyCompany;
GO
SELECT COUNT(DISTINCT orderid) AS NumberOfOrdersMade, customerid AS
CustomerID
FROM tblItems_Ordered
GROUP BY customerid
HAVING COUNT(DISTINCT orderid) > 1
GO
When outside of the COUNT
, the DISTINCT
will eliminate duplicate rows from a result set, which will have no effect in your query because you are doing a GROUP BY
. When inside the COUNT
, DISTINCT
will limit the count to unique values of the column that you pass to the count function. Thus, it makes more sense to use an orderid column instead of customerid when you're aliasing it as NumberOfOrdersMade.
how to use count and distinct together
You can use distinct
inside count:
select count(distinct country)
from customers;
Which is equivalent to:
select count(*)
from (
select distinct country
from customers
where country is not null
) t;
COUNT DISTINCT with CONDITIONS
You can try this:
select
count(distinct tag) as tag_count,
count(distinct (case when entryId > 0 then tag end)) as positive_tag_count
from
your_table_name;
The first count(distinct...)
is easy.
The second one, looks somewhat complex, is actually the same as the first one, except that you use case...when
clause. In the case...when
clause, you filter only positive values. Zeros or negative values would be evaluated as null
and won't be included in count.
One thing to note here is that this can be done by reading the table once. When it seems that you have to read the same table twice or more, it can actually be done by reading once, in most of the time. As a result, it will finish the task a lot faster with less I/O.
Related Topics
How to Execute SQL Statements Saved in a Table with T-Sql
MySQL Scoping Problem with Correlated Subqueries
Search Criteria Difference Between Like VS Contains() in Oracle
Xml Oracle:Multiple Child Node Extract
Display Column Name with Max Value Between Several Columns
Query to List SQL Server Stored Procedures Along with Lines of Code for Each Procedure
Why Even Use *Db.Exec() or Prepared Statements in Golang
How to Sort a Linked List in SQL
Oracle Convert Seconds to Hours:Minutes:Seconds
How to Select Bottom Most Rows
Oracle Select for Update Behaviour
How to Find the Worst Performing Queries in SQL Server 2008
Using SQL Localdb in a Windows Service
Best-Performance Query for "Select Max in Group"
Pl/SQL Performance Tuning for Like '%...%' Wildcard Queries