So far I have this as my SQL Query:
SELECT value, count(value) over () as TOTAL from listofvalues GROUP BY value
The result look like that:
value|TOTAL
IS18-0001| 6
IS18-0002| 6
IS18-0003| 6
IS18-0004| 6
IS18-0005| 6
IS18-0006| 6
What I want the result to look like would be more like that:
value|TOTAL
IS18-0001| 6
IS18-0002|
IS18-0003|
IS18-0004|
IS18-0005|
IS18-0006|
Basically I want to LIMIT the total to 1, but can't seems to find a solution, any idea? Is that even possible?
You can check for row_number and set other rows to null (or '')
SELECT value,
case row_number() over () when 1 then count(value) over () else null end as TOTAL
from listofvalues GROUP BY value
It would seem that you don't need the group by, so I would start with:
SELECT value,
(CASE WHEN row_number() over (order by value) = 1
THEN count(*) over ()
END) as TOTAL
FROM listofvalues ;
The GROUP BY is only needed if you have multiple rows with the same value. If you have duplicates, your query is doing something quite specific -- counting the number of distinct values, while listing each value exactly once. It is not counting the number of rows in the original data.
Please try below query. I just added a row number to your query.
Select Value,case when RN=1 then Total else '' END Total
From(
select value,Cast(Total as varchar) Total,ROW_NUMBER() over (order by value) RN
from(
SELECT value,
count(value) over () as TOTAL
from #listofvalues GROUP BY value
)A)B
