Calculate sum of SQL Top 10

I have an SQL statement which returns the Top 10 states with the number of visitors

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

So far this is what I have

state| visitors

MD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9

But what i need is to calculate the total for the Visitors column in my SQL so that is like so

MD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100

I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?

Thank you

You can play with rollup and cube to get your result. Here is a sample for you to get start:

SELECT

ISNULL(state,'top10'),SUM(mycount)as top10Sum,SUM(myavg)as top10avg

FROM

tab1

GROUP

BY state

WITH

rollup

UNION

ALL

SELECT

'all states',SUM(mycount)as top10Sum,SUM(myavg)as top10avg

FROM

tab1|||

where would i place this query

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

|||

Somehting like this:

SELECT

t3.state, t3.mycount1FROM(

SELECT

TOP 10 State,COUNT(*)as mycount1FROM Customer

GROUPBY state

ORDERBYCOUNT(*)DESC) t3

UNION

SELECT

'top10'as state,SUM(t1.mycount1)as mycount1FROM(

SELECT

TOP 10 State,COUNT(*)as mycount1FROM CustomerGROUPBY stateORDERBYCOUNT(*)DESC)as t1

UNION

ALL

SELECT

'ALL',COUNT(customer)as mycount1

FROM

Customer|||

this example you gave me is not working...

basically I need a way to combine the following two SQL statements to have one final result

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

SELECT 'Total Top 10', SUM(t1.Visitors)
FROM
(SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC )t1|||

declare @.result table

(

States varchar(100),

Visitors int

)

insert into @.result (States, Visitors)

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

select *

from @.result

union all

select 'Total Top 10', sum(Visitors) from @.result

|||thanx you're a life saver|||

Hello,

I don't know why it is not working for you since I don't have any data from you to test.

Here is something I used to test the script.

CREATE TABLE [dbo].[tab1$]([state] [nvarchar](50), [customer] [nvarchar](50) )Sample Data:INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c1')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c2')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c3')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c4')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c5')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c6')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c7')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c8')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c9')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c10')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b1')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b2')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b3')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b4')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b5')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b6')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b7')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b8')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b9')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b10')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b11')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b12')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b13')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b14')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c1')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c2')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c3')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c4')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c5')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d1')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d2')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d3')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d4')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d5')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d6')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d7')

Here is the SQL script (I chose top 2 instead):

SELECT t3.state, t3.mycount1FROM(SELECT TOP 2 State,COUNT(*)as mycount1FROM tab1$GROUP BY stateORDER BYCOUNT(*)DESC) t3UNION SELECT'top 10'as state,SUM(t1.mycount1)as mycount1FROM (SELECT TOP 2 State,COUNT(*)as mycount1FROM tab1$GROUP BY stateORDER BYCOUNT(*)DESC)as t1UNIONALLSELECT'ALL',COUNT(customer)as mycount1FROM tab1$

|||

When i try to use this @.result table on this query I get the following error

SELECT TOP (10) t1.City City,t1.State State,SUM(t1.Population) Population , SUM(t1.Visitors) Visitors
FROM (
SELECT Customer.zip Zipcode, COUNT(Zipcode) Visitors,Census.city,Census.State,Census.Population
FROM [Customer] JOIN [Census Test Data] Census ON Customer.zip = Census.zipcode
WHERE Customer.month = '8' AND Customer.year = '2006'
GROUP BY Customer.zip, Census.city,Census.State,Census.Population
) t1
GROUP BY t1.city,t1.State
ORDER BY Visitors DESC

ERROR: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

How do i make the values match