Sql: Count Distinct Values from One Column Based on Multiple Criteria in Other Columns

Count distinct values based on criteria in multiple columns

Use COUNT DISTINCT with CASE:

SELECT
    Username,
    DistinctCount = COUNT(DISTINCT CASE WHEN Amount <> 0 THEN Type END)
FROM #tbl
GROUP BY Username;

The difference with using WHERE clause is that, this will return rows with 0 DistinctCount, while the WHERE clause will not.

DEMO

SQL: Count distinct values from one column based on multiple criteria in other columns

You can have a conditional count(distinct) by using this code:

SELECT Test, COUNT(DISTINCT "Bug ID") AS "Total Bugs",
count(distinct (CASE WHEN "Status" <> 'Closed' THEN "Bug ID" END)) as "Open Bugs"
FROM Table1
GROUP BY Test

The case statement checks the condition. When true, it returns the Bug ID. When not present, it defaults to NULL, so the id does not get counted.

SQL: Count distinct column combinations with multiple conditions

With this query:

select distinct state, storeid, item
from mytable
where date between '2020-01-01' and '2020-03-31' and units > 0

you get all the distinct combinations of state, storeid and item under your conditions and you can aggregate on it:

select state, count(*) q1total
from (
  select distinct state, storeid, item
  from mytable
  where date between '2020-01-01' and '2020-03-31' and units > 0
) t
group by state

See the demo.

Results:

> state    | q1total
> :------- | ------:
> new york |       3
> oregon   |       2

Count distinct records in one column with multiple values in another column

First, you shouldn't be storing lists of things in a string.

But, sometimes one is stuck with this format. In your example, you seem to have a table with all possible values. If so you can use a join:

select e.col1, count(e2.col2)
from example e left join
     example e2
     on charindex(e.col1, e2.col2) > 0
group by e.col1;

Note: this counts rows containing the value rather. If multiple values appear in a single row, the query is a bit more complicated.

How To Count Distinct Values Based on Two Different Criteria?

You can achieve that with a filtered aggregation:

SELECT "School"."name" AS "School", 
        count(distinct "public"."users"."id") AS "count_1", 
        -- the following only counts users where the email column does not contain the value gmail
        count(distinct users.id) filter (where email not like '%gmail%') AS "count_2" 
FROM "public"."users"
LEFT JOIN "public"."user_roles" "User Roles" ON "public"."users"."id" = "User Roles"."user_id"
LEFT JOIN "public"."roles" "Role" ON "User Roles"."role_id" = "Role"."id"
LEFT JOIN "public"."schools" "School" ON "User Roles"."school_id" = "School"."id"
WHERE ("Role"."name" = 'Student'
   AND "public"."users"."deleted_at" IS NULL
   AND "public"."users"."activated_at" IS NOT NULL
   AND NOT (lower("public"."users"."email") like '%yopmail%'))
GROUP BY "School"."name"
ORDER BY "School"."name" ASC

SQL count of distinct records against multiple criteria

Just tested this and provides your desired result:

SELECT appid, licenseid, COUNT(DISTINCT userID)
FROM usage
WHERE dateUsed BETWEEN @from AND @dateTo
GROUP BY appid, licenseid

Count distinct values based on multiple criteria

Unique 'Descending'

• It is assumed that the table (one row of headers) starts in cell A1 and that the header is already written in the first cell of the destination (resulting) column (dCol).
• Adjust the worksheet name (wsName) and the destination column (dCol).
• The Delimiter has to be a string that is not contained within the data.

Option Explicit

Sub GetUnique()
    
    Const wsName As String = "Sheet1"
    Const dCol As String = "C"
    Const Delimiter As String = "|"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim srg As Range
    With ws.Range("A1").CurrentRegion
        Set srg = .Resize(.Rows.Count - 1, 2).Offset(1)
    End With
    Dim sData As Variant: sData = srg.Value
    
    Dim rCount As Long: rCount = srg.Rows.Count
    Dim dData As Variant: ReDim dData(1 To rCount, 1 To 1)
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim r As Long
    Dim cString As String
    
    For r = rCount To 1 Step -1
        cString = sData(r, 1) & Delimiter & sData(r, 2)
        If dict.Exists(cString) Then
            dData(r, 1) = 0
        Else
            dict.Add cString, Empty
            dData(r, 1) = 1
        End If
    Next r
    
    Dim drg As Range: Set drg = srg.Resize(, 1).EntireRow.Columns(dCol)
    drg.Value = dData

    With drg
        .Resize(ws.Rows.Count - rCount - 1).Offset(rCount).ClearContents
    End With
    
End Sub

Counting Distinct Records Using Multiple Criteria From Another Table In MySQL

You were close.

Try something like

SELECT  COUNT(Recipe.ID) Answer
FROM    Recipe INNER JOIN
        Ingredient olives ON olives.RecipeID=Recipe.ID INNER JOIN
        Ingredient mushrooms ON mushrooms.RecipeID=Recipe.ID 
WHERE   olives.Name='Olives'
AND     mushrooms.Name='Mushrooms'
AND     olives.Amount = 1
AND     mushrooms.Amount = 2

You can join to the same table twice, all you need to do is give the table an appropriate alias.

---

Related Topics