Sql-server – SQL Server count value of column

countsql server

I call the table is [vDemo]
[I call the table is [vDemo]]1

i want count MaLC by TenNV, result is
enter image description here

if record 13 MaLC is L then Count(x) = 16, Count(L) = 1

Best Answer

You can use SUM or COUNT functions to do this, with a case expression:

select
   TenNV
   ,SUM(count_x) as count_x
   ,SUM(count_L) as count_l
from
(
select 
  TenNV
  ,Case when MaLC = 'x' then 1 else 0 end as count_x
  ,Case when MaLC = 'L' then 1 else 0 end as count_L
from vDemo
) A
group by TenNV

or with COUNT:

select
   TenNV
   ,COUNT(count_x) as count_x
   ,COUNT(count_L) as count_l
from
(
select 
  TenNV
  ,Case when MaLC = 'x' then MaLC else NULL end as count_x
  ,Case when MaLC = 'L' then MaLC else NULL end as count_L
from vDemo
) A
group by TenNV

output of this:

TenNV   count_x count_l
Cam     5       0
Ngo     17      0

dbfiddle here