Distinct Count Query

Distinct Count Query

sql count(distinct multiple columns)
sql select distinct values and count of each
count(distinct oracle)
sql count occurrences of distinct values
count(distinct mysql)
distinct count excel
sql distinct multiple columns
where distinct sql

I have a table below:

 Item    Status1    Status2
-----------------------------
  A       Good       NULL
  A       Good       NULL
  A       Good       NULL
  A       Bad        Good

  B       Bad        Good
  B       Good       NULL

  C       Good       NULL
  C       Good       NULL      
  C       Good       NULL

  D       Bad        Good

Now, I'm thinking off writing a query which gives me the result below:

 Item     Good     Bad
-----------------------------
  A        4        1
  B        2        1
  C        3        0
  D        1        1

Distinct in the Item column and the count of Good and Bad for each Item where NULL is not counted.

The column name can be of anything (I just kept it as Good and Bad in my second table).

Any suggestions/ideas on how to achieve my desired results?


Use UNION ALL & do aggregation :

select item, sum(status = 'good'), sum(status = 'bad')
from (select item, status1 as status
      from table t
      union all
      select item, status2
      from table t
     ) t
group by item;

SQL COUNT() with DISTINCT, The DISTINCT can come only once in a given select statement. Syntax : COUNT(​DISTINCT expr,[expr]) Example : To get unique number of rows  COUNT () function with distinct clause. SQL COUNT () function with DISTINCT clause eliminates the repetitive appearance of the same data. The DISTINCT can come only once in a given select statement.


You can use union all and conditional aggregation

select item, count(case when status1='good' then 1 end) as good,
count(case when status1='bad' then 1 end) as bad
from
(
select item , status1 from tablename
union all
select item , status2 from tablename
)A group by item

SQL Server query - Selecting COUNT(*) with DISTINCT, Count all the DISTINCT program names by program type and push number. SELECT COUNT(DISTINCT program_name) AS Count,  Overview of the SQL Count Distinct Function SQL Count Function. We use SQL Count aggregate function to get the number of rows in the output. Suppose we have a Difference between SELECT COUNT, COUNT (*) and SQL COUNT distinct. It returns the total number of rows after satisfying Execution Plan


use union and case when

select Item, sum(case when status = 'good' then 1 else 0 end) as good, 
 sum ( case when status = 'bad' then 1 else 0 end) as bad
from (select Item, Status1 as status
      from table_name
      union all
      select Item, Status2
      from table_name
     ) t
group by Item;

Overview of the SQL Count Distinct Function, Now, execute the following query to find out a count of the distinct city from the table. 1. 2. SELECT COUNT(  Count distinct records that contain NULL values. The query below is the inner query we just saw which shows a list of distinct values in the ReportsTo column. Note that when we run this query alone, NULL is displayed in the result set, but this NULL value is not counted as a distinct value in the example query shown above when we count the number of distinct values.


There's no need for UNION, simply apply some logic.

select Item
  ,sum(case when Status1 = 'Good' then 1 else 0 end +
       case when Status2 = 'Good' then 1 else 0 end) as good
  ,sum(case when Status1 = 'Bad' then 1 else 0 end +
       case when Status2 = 'Bad' then 1 else 0 end) as bad
from tab
group by Item

or

select Item
  ,count(case when Status1 = 'Good' then 1 end) +
   count(case when Status2 = 'Good' then 1 end) as good
  ,count(case when Status1 = 'Bad' then 1 end) +
   count(case when Status2 = 'Bad' then 1 end) as good
from tab
group by Item

COUNT (Transact-SQL), If not specified, the function treats all rows of the query result set as a COUNT(​DISTINCT expression) evaluates expression for each row in a  SELECT DISTINCT. Statement. SELECT DISTINCT returns only distinct (different) values. SELECT DISTINCT eliminates duplicate records from the results. DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc. DISTINCT operates on a single column. DISTINCT for multiple columns is not supported. The definitive guide. for data professionals.


You can use sub-query and then apply sum function in outer query

select distinct(item) as item, sum(S1G+S2G) as Good,sum(S1B+S2B) as Bad from (  select item, CASE WHEN status1 ='Good' THEN 1 ELSE 0 END as S1G, CASE WHEN status2 ='Good' THEN 1 ELSE 0 END as S2G,  CASE WHEN status2 ='Bad' THEN 1 ELSE 0 END as S2B,  CASE WHEN status1 ='Bad' THEN 1 ELSE 0 END as S1B from t1 ) as b group by item

Here is demo

SQL SELECT DISTINCT Statement, Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access in our  selecting distinct product and display count per product for another answer about this type of question this is my another answer for getting count of product base on product name distinct like this sample below:


COUNT DISTINCT and COUNT UNIQUE functions, If the Projection clause does not specify the DISTINCT or UNIQUE keyword of the SELECT statement, the query can include multiple COUNT functions that each  The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.


COUNT (DISTINCT) and Other DISTINCT Aggregates, Also, a query that uses a single DISTINCT aggregate consumes fewer resources than a query with multiple DISTINCT aggregates. Vertica executes queries with  Solved: i am trying to group by in power query but i only want it to count distinct on one column. this is the current m code #"Grouped


SQL Distinct, SQL Count Distinct, SQL Select Distinct, We will use the inner select query to first find out the unique gender values and the use count function on it. SELECT Count(*) As DistinctGender FROM (SELECT​