SQL Countifs function

Question

Hello,

I reach a dead end. I have a table with piecemark_1, piecemark_2, and weld_type.

I wanted to have a final output as per image show.

In excel I would usually use the formula "COUNTIFS+COUNTIFS" but now my data is getting bigger.

I tried to UNION both piecemark together from column piecemark_1 and piecemark_2 to create another table but I do not know how to countifs from another table.

I was wondering how can I solve this issue.

score 2 · Answer 1 · answered Sep 09 '21 at 09:52

2

You seems want :

select piecemark, SUM(CASE WHEN WELD_TYPE = 'SW' THEN 1 ELSE 0 END) AS SW,
       SUM(CASE WHEN WELD_TYPE = 'FW' THEN 1 ELSE 0 END) AS FW, COUNT(*) AS Total
from t cross apply
     ( values (piecemark_1), (piecemark_2) 
     ) tt(piecemark)
group by piecemark
having count(*) > 1;

For all piecemark you need to exclude having clause.

answered Sep 09 '21 at 09:52

Yogesh Sharma

49,870
5
26
52

thank you sir. it worked. but can I ask, what is the tt(piecemark) – Fauzi Yahaya Sep 09 '21 at 10:13
@FauziYahaya... `tt` is alias with column name `piecemark`. – Yogesh Sharma Sep 09 '21 at 10:15

SQL Countifs function

1 Answers1