case when with count and combining similar values in sql
SELECT
data.id,
data.lastname,
data.firstname,
SUM(data.WebData) AS WebData,
SUM(data.InternalData) AS InternalData,
SUM(data.countid) AS Countid
FROM
(
SELECT id,lastname,
firstname,datasource,
CASE WHEN Datasource = 'Web' THEN Count(Datasource) ELSE 0 END WebData,
CASE WHEN Datasource = 'Internal' THEN Count(Datasource)ELSE 0 END InternalData,
count(id) AS Countid
FROM Table
GROUP BY
id,lastname,firstname,datasource
) AS data
GROUP BY
data.id,data.firstname,data.lastname