I have to inner join table A and B on key and do a group by to get counts.

• Count 1: if all Ind1, Ind2,Ind3 from Table A is 'A' it should be counted in Count 1 Column
• Count 2: if any of Ind1,Ind2,Ind3 from Table A is not 'A' it should be counted in Count 2 Column
• Count 3: Sum of Count 1 and 2

Expected output:

CodePudding user response：

In SQL Server I would do it with SUM(CASE WHEN) instead of counting, and an outer query for Count3 because it is a bit cleaner:

SELECT *, Count1   Count2 AS Count3
FROM
(
SELECT Key, 
SUM(CASE WHEN Ind1 = 'A' AND Ind2 = 'A' AND Ind3 = 'A' THEN 1 ELSE 0) AS Count1, 
SUM(CASE WHEN Ind1 != 'A' OR Ind2 != 'A' OR Ind3 != 'A' THEN 1 ELSE 0) AS Count2
FROM
A
JOIN
B ON A.Key = B.Key
GROUP BY Key
) q

CodePudding user response：

Using conditional counts.

SELECT a.Key, b."Desc",
COUNT(CASE WHEN Ind1 = 'A' AND Ind1 = Ind2 AND Ind1 = Ind3 THEN 1 END) AS "Count 1",
COUNT(CASE WHEN NOT(Ind1 = 'A' AND Ind1 = Ind2 AND Ind1 = Ind3) THEN 1 END) AS "Count 2",
COUNT(1) AS "Count 1   Count 2"
FROM "Table A" a
LEFT JOIN "Table B" b ON b.Key = a.Key
GROUP BY a.Key, b."Desc"