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
But what i need is to calculate the total for the Visitors column in my SQL so that is like so
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 top10avgFROM
tab1GROUP
BY stateWITH
rollupUNION
ALLSELECT
'all states',SUM(mycount)as top10Sum,SUM(myavg)as top10avgFROM
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 CustomerGROUPBY 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 t1UNION
ALLSELECT
'ALL',COUNT(customer)as mycount1FROM
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
