How to sum count value in SQL Server ?
I have table 1. I want to sum count value.
How to do that ?
SELECT Top 10 count(d.name) as countname,d.name as name ,sum(count(d.name)) as sumcount
FROM table 1 as d
group by d.name order by count(d.name) desc
I want to display countname, name, sumcount. How to do that ?
Not sure I'm understanding your question, but if you're just looking to get the sum of all the count(d.name) values, then this would do that for you:
select sum(countname) as TotalCount
from
(
SELECT Top 10
count(d.name) as countname,
d.name as name
FROM [table 1] as d
group by d.name
order by count(d.name) desc
)a
add with rollup to the end of your query
with rollup work with the order by clause in MS SQL? It doesn't in MySQL, which makes it hard to ensure you have the top number of occurrences of count(d.name) before adding them up.Expanding on the answer provided by @Shark, MySQL syntax will look like the following:
set @TotalCount = (select sum(countname) from (
select count(d.name) as countname, d.name as name
from table 1 as d
group by name
order by countname desc
limit 10
) a);
select count(d.name) as countname, d.name as name, @TotalCount
FROM table 1 as d
group by name
order by countname desc
limit 10;
You may need to look up MS SQL syntax for setting local variables and limits.
SELECT TOP 10 COUNT(*) AS countname, d.name FROM table 1 as d GROUP BY d.name ORDER BY COUNT(*) DESCwill give you the top 10 most usedd.namevalues as well as the number of times they're used.