CODEWITHSUNDEEP
sqlsql-server
jitendra
jitendra

Reputation: 209

Group BY on Condition basis

I have data in following way....

ColumnA ColumnB
7675    22838
7675    24907
7675    NULL

I want the results in following way.....

ColumnA ColumnB
7675    2  (need total count for Not Null value)
7675    0  (need count 0 for NULL value)

Upvotes: 0

Views: 286

Answers (4)

Andriy M
Andriy M

Reputation: 77657

You could introduce a calculated column indicating whether ColumnB is null or not and use it as a grouping criterion together with ColumnA:

SELECT
  t.ColumnA,
  ColumnB = COUNT(t.ColumnB)
FROM
  dbo.YourTable AS t
CROSS APPLY
  (SELECT CASE WHEN t.ColumnB IS NULL THEN 1 ELSE 0 END) AS x (SubGroup)
GROUP BY
  t.ColumnA,
  x.SubGroup
ORDER BY
  t.ColumnA,
  x.SubGroup
;

The COUNT(t.ColumnB) expression would always be NULL for a null subgroup, and for the corresponding non-null subgroup it would return the number of the non-null entries.

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25842

you could easily do with a count and sum which may be faster if there are a lot of rows rather than selecting all of the rows twice with a UNION

SELECT columna, columnb, SUM(mycount) 
FROM
(   SELECT *, COUNT(columnb) as mycount 
    FROM test
    GROUP BY columnb
)t
GROUP BY mycount
ORDER BY CASE WHEN mycount = 0 THEN 1 ELSE 2 END DESC;

Fiddle Demo

Upvotes: 0

Lamak
Lamak

Reputation: 70638

SELECT ColumnA, COUNT(ColumnB) ColumnB
FROM YourTable
GROUP BY ColumnA
UNION ALL
SELECT ColumnA, 0
FROM YourTable
WHERE ColumnB IS NULL
GROUP BY ColumnA

Upvotes: 4

juergen d
juergen d

Reputation: 204746

select columnA, 
       count(columnB) as non_null_count, 
       sum(columnB is null) as null_count
from your_table
group by ColumnA

Upvotes: 0

Related Questions