CODEWITHSUNDEEP
sqlsql-server
Ryan
Ryan

Reputation: 155

SQL DISTINCT for 2 Columns WHERE 3rd column = value

I'm trying to Select the Count of Distinct Columns Col 1 & Col 2 Where Col 3 = "Complete".

Firstly I don't know how to make Distinct apply to Col 1 & Col 2 together as opposed to Distinct about their own columns. Secondly how to exclude Col 3 from the Distinct..

SELECT COUNT(*) AS Count From 
(Select Distinct DP, RN From ECount 
Where ET = 'Complete') as rows

Any thoughts?

example

col1    col2    col3    
DP01    RN01    Complete    yes
DP01    RN02    Incomplete  
DP02    RN03    Complete    
DP02    RN03    Incomplete  
DP01    RN04    Complete    yes
DP02    RN05    Complete    yes
DP03    RN06    Incomplete

Result = 3

Upvotes: 1

Views: 154

Answers (3)

Tiefan Ju
Tiefan Ju

Reputation: 520

SELECT
    Count(*) AS Count
FROM
    (
        SELECT
            *
        FROM
            (
                SELECT
                    *
                FROM
                    `ECount`
                ORDER BY
                    col3 DESC
            ) AS StrongIncomplete
        GROUP BY
            col1,
            col2
    ) AS CompleteCut
WHERE
    CompleteCut.col3 = 'Complete'

There are 3 SELECT statements.

The first one rearranges the table that 'Incomplete' is prior than 'Complete' in Col3.

The second one removes rows duplicated in Col1, Col2.

The third one removes rows where Col3 = 'Incomplete'

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

just don't select ET in subquery

SELECT COUNT(*) AS Count
From  ( 
        Select Distinct DP, RN 
        From ECount 
        Where ET = 'Complete'
) as rows

Upvotes: 0

flygaio
flygaio

Reputation: 121

I don't think you need to include Column 3 (aka, col ET) in the SELECT part, you can just use it in the WHERE statement directly.

So in your example:

SELECT COUNT(*) AS Count FROM 
  (SELECT DISTINCT DP, RN FROM ECount 
   WHERE ET = 'Complete'
  ) AS rows

Upvotes: 1

Related Questions