How to output the same column twice, effected by different WHERE clauses with the COUNT function

Question

I need to output one column twice, under different conditions and aliases. Note: this column is also the PKEY of each table I pull from

For simplicity, lets say the column i want to output twice is called COL1

-The first version of COL1 I want to output contains rows where COL1 is a unique value Lets call this "UniqueCol1"

-The second version of COL1 I want to output contains rows where COL1 occurs more than once in the data set Lets call this "NonUniqueCol1"

The part of my query that's giving me trouble is that a bunch of other different conditions need to be true for each version of COL1, joins are involved, and I don't know exactly where to input the COUNT conditions that I believe I need.

The code I dropped below is essentially the query I have now.

Select T1.COL2, T1.COL3, count(T1.COL3) as Counted
FROM table1 T1
INNER JOIN table2 T2
ON T1.COL1 = T2.COL1
AND T1.COL2 = T2.COL2
AND T1.COL3 = T2.COL3
WHERE EXISTS 
(
Select 1 
FROM
table2 TT2
inner join table1 TT1
ON TT2.COL1 = TT1.COL1
WHERE TT2.COL4 ='A'
AND TT2.COL5 ='3'
AND TT2.COL6 <> '55555555'
AND TT1.COL7 = 'M'
AND TT2.COL2 = T1.COL2
AND TT2.COL3 = T1.COL3

)
Group BY T1.COL2, T1.COL3
HAVING count(T1.COL2) >=3;

The result of this query is something like this:

COL2      COL3       Counted
C0067     4355       3
D8120     5777       4
C4444     9009       3
TBC09     1133       3
C0999     4617       3

Out of those entries Counted in COL3, there are two different COL1's

The unique COL1 value
The COL1 value that shows up more than once

My desired result is to print COL2, COL3, UniqueCOL1, and NonUniqueCOL1

Thank you for reading, help would be so much appreciated as SQL is not my strong-suit.

*Sounds* like your after a conditional aggregate? For example `COUNT(CASE WHEN YourCol = 'YourValue' THEN 1 END)` — Thom A, Jul 24 '19 at 14:33
If I could put AND statements in those brackets then that may be what I need. — DB2Jacob, Jul 24 '19 at 14:35

score 0 · Answer 1 · answered Nov 11 '19 at 22:41

I'm not sure you have explained what you are wanting to achieve very well, but here is some code that might help, or might not.

WITH table1(col1, col2, col3) AS (values ('x','a','a'), ('x','a','a'),('y','a','a'), ('z','a','a'),(null,'a','a'),('x','b','a'),('x','b','a'))
SELECT
    T1.COL2
,   T1.COL3
,   count(T1.COL1)          as NonUniqueCOL1
,   count(DISTINCT T1.COL1) as UniqueCOL1
FROM table1 T1
GROUP BY
    COL2, COL3

with returns

COL2 |COL3 |NONUNIQUECOL1 |UNIQUECOL1 |
-----|-----|--------------|-----------|
a    |a    |4             |3          |
b    |a    |2             |1          |

How to output the same column twice, effected by different WHERE clauses with the COUNT function

1 Answers1