Distinct Count Query
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?
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
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