Bueno el día de la semana (LUN, MAR, MIE, ETC.) esta indicado por select datepart(dw, getdate()), hoy en mi maquina es 5, Jueves, tambien depende del SET DATEFIRST
SELECT CASE
WHEN datepart(dw, getdate()) = 1 THEN 'DOM'
WHEN datepart(dw, getdate()) = 2 THEN 'LUN'
WHEN datepart(dw, getdate()) = 3 THEN 'MAR'
WHEN datepart(dw, getdate()) = 4 THEN 'MIE'
WHEN datepart(dw, getdate()) = 5 THEN 'JUE'
WHEN datepart(dw, getdate()) = 6 THEN 'VIE'
WHEN datepart(dw, getdate()) = 7 THEN 'SAB' END AS DIA_SEMANA,
COUNT(*)
FROM MyTabla
GROUP BY
CASE
WHEN datepart(dw, getdate()) = 1 THEN 'DOM'
WHEN datepart(dw, getdate()) = 2 THEN 'LUN'
WHEN datepart(dw, getdate()) = 3 THEN 'MAR'
WHEN datepart(dw, getdate()) = 4 THEN 'MIE'
WHEN datepart(dw, getdate()) = 5 THEN 'JUE'
WHEN datepart(dw, getdate()) = 6 THEN 'VIE'
WHEN datepart(dw, getdate()) = 7 THEN 'SAB' END
