I need to show the total count for a particular field with my select statement. But this should be shown only once for each VID. I wrote the following to get the count, but it displays it for every record. Is there away to display it only in one row for a specific VID?
I understand the requirement doesn't make much sense when you look at the final result. But this is something I need for a dashboard that we are developing for the end user.
CREATE TABLE #t1([VID] [varchar](100) NULL, [OrdDate] [datetime] NOT NULL,
[Prg1] [varchar](10) NULL
)
ON [PRIMARY]
insert into #t1 values('1111','2022-01-10 13:37:06.000','P1')
insert into #t1 values('1111','2022-01-10 13:37:06.000','P2')
insert into #t1 values('1111','2022-01-10 13:37:06.000','P3')
insert into #t1 values('2222','2022-01-10 13:37:06.000','P3')
insert into #t1 values('2222','2022-01-10 13:37:06.000','P4')
insert into #t1 values('2222','2022-01-10 13:37:06.000','P5')
insert into #t1 values('3333','2022-01-10 13:37:06.000','P2')
insert into #t1 values('3333','2022-01-10 13:37:06.000','P3')
insert into #t1 values('3333','2022-01-10 13:37:06.000','P3')
insert into #t1 values('3333','2022-01-10 13:37:06.000','P1')
insert into #t1 values('3333','2022-01-10 13:37:06.000','P3')
insert into #t1 values('3333','2022-01-13 13:37:06.000','P1')
insert into #t1 values('3333','2022-01-10 13:37:06.000','')
select t1.*, (select count(VID) from #t1 t2 where t2.VID=t1.VID and t2.[Prg1]='P1')
from #t1 t1
drop table #t1
With the above code I get the count for each row (Ex: there are 3 rows for VID 1111. I want the count shown only in one row.
October 26, 2022 at 9:38 pm
Does this work? Replace the NULL with 0 if preferred.
SELECT t2.VID, t2.OrdDate, t2.Prg1,
CASE WHEN t2.RowNum = 1 THEN VIDCount ELSE NULL END AS VIDCount
FROM (
SELECT *,
COUNT(*) OVER (PARTITION BY t1.VID) as VIDCount,
ROW_NUMBER() OVER (PARTITION BY t1.VID ORDER BY t1.Prg1, t1.OrdDate) AS RowNum
FROM #t1 AS t1
) AS t2
October 27, 2022 at 1:54 pm
Thanks for the reply. How do I add the where condition to it (where t1.[Prg1]='P1')?
October 27, 2022 at 2:52 pm
Just add it in:
SELECT t2.VID, t2.OrdDate, t2.Prg1,
CASE WHEN t2.RowNum = 1 THEN VIDCount ELSE NULL END AS VIDCount
FROM (
SELECT *,
COUNT(*) OVER (PARTITION BY t1.VID) as VIDCount,
ROW_NUMBER() OVER (PARTITION BY t1.VID ORDER BY t1.Prg1, t1.OrdDate) AS RowNum
FROM #t1 AS t1
WHERE t1.Prg1 = 'P1'
) AS t2
GO
SELECT t2.VID, t2.OrdDate, t2.Prg1,
CASE WHEN t2.RowNum = 1 THEN VIDCount ELSE NULL END AS VIDCount
FROM (
SELECT *,
COUNT(*) OVER (PARTITION BY t1.VID) as VIDCount,
ROW_NUMBER() OVER (PARTITION BY t1.VID ORDER BY t1.Prg1, t1.OrdDate) AS RowNum
FROM #t1 AS t1
) AS t2
WHERE t2.Prg1 = 'P1'
However, you get two different results, because the counting is done differently. I'm not sur which one you need. I assume the first, as you want to know the count by the VID.
October 27, 2022 at 3:10 pm
That will reduce the displayed no of rows. I still want to see all the rows and the where condition should be applied only for the count.
I failed to realise the importance of the Prg1 filter. Is this better? It returns all the rows, but only counts where Prg1 = 'P1'.
I had to choose an order by column for the rownumber so this means the empty string Prg1 value gets the count for VID 3333, and as the count is zero for VID 2222 the count is by the next lowest Prg1 value. Is this what you expect to see?
SELECT t2.VID, t2.OrdDate, t2.Prg1,
CASE WHEN t2.RowNum = 1 THEN VIDCount ELSE NULL END AS VIDCount
FROM (
SELECT *,
SUM(CASE WHEN t1.Prg1 = 'P1' THEN 1 ELSE 0 END) OVER (PARTITION BY t1.VID) as VIDCount,
ROW_NUMBER() OVER (PARTITION BY t1.VID ORDER BY t1.OrdDate, t1.Prg1) AS RowNum
FROM #t1 AS t1
) AS t2
ORDER BY t2.VID, t2.VIDCount DESC
October 27, 2022 at 6:57 pm
Excellent. That's exactly what I was expecting.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply