I need to find counts of transactions for data providers. I need for some of the data providers to roll up into one single result row and the rest to display just there transaction counts. Instead what I keep getting is a single row for every single data provider.
I tried the following statement. I need it to add to count for x if a datasourceid is x,y, or z. And to its own count if not x,y, or z
SELECT
CASE
WHEN C.Datasourceid IN (x, y, z) THEN x
ELSE C.Datasourceid
END AS DatasoureID,
CASE
WHEN D.DatasourceName IN ('Mips', 'Yips', 'Zips') THEN 'Mips'
ELSE D.DatasourceName
END AS DatasourceName,
COUNT(*) AS Transactions
FROM
ClaimView C, DatasourceView D
AND C.ReportDate BETWEEN '07/01/2023' AND '6/30/2024'
DatasourceActiveIndicator = 'Y'
GROUP BY
C.Datasourceid, DatasourceName
ORDER BY
COUNT(*) DESC
This is what the results would look like without combining some of the datasources:
enter image description here
And this is what I need it to look like:
enter image description here
5
Change your GROUP BY clause to
GROUP BY
CASE
WHEN C.Datasourceid IN (x, y, z) THEN x
ELSE C.Datasourceid
END AS DatasoureID,
so you merge the three groups into one, not just in the selection, but the aggregation too.
You can use a derived table and then group by on the columns of derived table. For example:
DROP TABLE IF EXISTS #DataPointSource
CREATE TABLE #DataPointSource (DataSourceId varchar(2) NOT NULL, DataSourceName varchar(100), Cnt int)
INSERT INTO #DataPointSource (DataSourceId, DataSourceName, Cnt)
VALUES
('a', 'Apple', 1000)
, ('b', 'Banana', 2000)
, ('x', 'Fruits', 5000)
, ('y', 'Orange', 500)
, ('z', 'Lemon', 500)
, ('d', 'Peach', 3000)
, ('e', 'Grapefruit', 2500)
select * from #DataPointSource
select tbl.DataSourceId, tbl.DataSourceName, SUM(tbl.Cnt)
from
(select
case when DataSourceId in ('x','y','z') then 'x' else DataSourceId end as DataSourceId,
case when DataSourceId in ('x','y','z') then 'Fruits' else DataSourceName end as DataSourceName,
Cnt
from #DataPointSource) as tbl
group by tbl.DataSourceId, tbl.DataSourceName
0