Table
COlumnA | ColumnB |
---|---|
KLAX | KONT |
KBUR | KJFK |
KSAN | KBUR |
KJFK | KPHX |
Count distinct columnA
Count distinct columnB
Return a table:
KLAX | 1 | 0 |
KBUR | 1 | 1 |
KSAN | 1 | 0 |
KJFK | 1 | 1 |
KONT | 0 | 1 |
KPHX | 0 | 1 |
I don’t think this can be done with 1 Select
I even tried temp tables. I just can not wrap my head around this one.
Any ideas?
Timothy Treaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
It appears that you want to report for distinct counts of the labels across both columns, in a single column. If so, then we can try the following union approach:
SELECT val, COUNT(*) AS cnt
FROM
(
SELECT ColumnA FROM yourTable
UNION ALL
SELECT ColumnB FROM yourTable
) t
GROUP BY val;
1
WITH Items AS
(
SELECT ColumnA As Item FROM MyTable
UNION
SELECT ColumnB FROM MyTable
)
SELECT Item
, (SELECT COUNT(*) FROM MyTable WHERE ColumnA = Item)
, (SELECT COUNT(*) FROM MyTable WHERE ColumnB = Item)
FROM Items
See it work here:
https://dbfiddle.uk/uHaP4yoq
1
One option is to use Union to get distinct labels and inner join labels to your table on either col_a or col_b and do the conditional aggregation using Case expressions:
-- S a m p l e D a t a :
Create Table tbl (col_a varchar(4), col_b varchar(4));
Insert Into tbl (col_a, col_b)
VALUES row('KLAX', 'KONT'),
row('KBUR', 'KJFK'),
row('KSAN', 'KBUR'),
row('KJFK', 'KPHX');
-- S Q L :
Select l.lbl,
Max(Case When l.lbl = t.col_a Then 1 Else 0 End) as cnt_a,
Max(Case When l.lbl = t.col_b Then 1 Else 0 End) as cnt_b
From ( Select col_a as lbl From tbl UNION Select col_b From tbl ) l
Left Join tbl t ON( l.lbl In(t.col_a, t.col_b) )
Group By l.lbl
/* R e s u l t :
lbl cnt_a cnt_b
---- ----- -----
KLAX 1 0
KBUR 1 1
KSAN 1 0
KJFK 1 1
KONT 0 1
KPHX 0 1 */
… and if you want to add total label count column ….
-- S Q L :
Select l.lbl,
Max(Case When l.lbl = t.col_a Then 1 Else 0 End) as cnt_a,
Max(Case When l.lbl = t.col_b Then 1 Else 0 End) as cnt_b,
--
Sum(Case When l.lbl = t.col_a Then 1 Else 0 End +
Case When l.lbl = t.col_b Then 1 Else 0 End) as cnt_lbl_total
From ( Select col_a as lbl From tbl UNION Select col_b From tbl ) l
Inner Join tbl t ON( l.lbl In(t.col_a, t.col_b) )
Group By l.lbl
/* R e s u l t :
lbl cnt_a cnt_b cnt_lbl_total
---- ----- ----- -------------
KLAX 1 0 1
KBUR 1 1 2
KSAN 1 0 1
KJFK 1 1 2
KONT 0 1 1
KPHX 0 1 1 */
See the fiddle here.
Union the two data sets, then aggregate:
select code, max(cola) as a, max(colb) as b
from
(
select columna as code, 1 as cola, 0 as colb from mytable
union all
select columnb as code, 0 as cola, 1 as colb from mytable
)
group by code
order by code;