Total Count in Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Total Count in Group By

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 04:39:06

In the query below if a voter casts a vote on a day a record is returned. If for example; voter_id 66 casts a vote on 5 days, it shows 5 rows with voter_ID 66 returned plus the dates that the votes were cast.

If voter_id 66 voted 10 times in one day and 1 time on each of the other 4 days, it still returns 5 records which is correct.

I would like to ultimately total the number of votes per voter_ID across all the days so there would be 1 record returned per voter_id containing the total number of days a vote or votes were cast and the voter_ID.

The voter is able to vote many times on the same day for different people but in this query it only counts as 1 vote which is working ok due to the group by voter_id and date stuff. I just need to talley up the number of days in which the particular voter voted.

Any help is appreciated

Phil

SELECT Voter_ID, YEAR(Vote_Time) AS Vote_Year,
MONTH(Vote_Time) AS Vote_Month,
DAY(Vote_Time) AS Vote_Day
FROM dbo.Votes
GROUP BY Voter_ID, YEAR(Vote_Time), MONTH(Vote_Time), DAY(Vote_Time)
ORDER BY Voter_ID, Vote_Year, Vote_Month, Vote_Day

This is the table:

[Vote_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Video_ID] [int] NULL ,
[Vote_Time] [datetime] NOT NULL ,
[Voter_ID] [int] NULL ,
[Voter_IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Voter_SMS] [bit] NOT NULL ,
[Voter_SMS_Phone_Number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Voter_SMS_Carrier] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vote_Active] [bit] NULL

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 04:55:20

try this

SELECT Voter_ID, Vote_Days = COUNT(*), Total_Votes = SUM(Vote_Cnt)
FROM
(
	SELECT 	Voter_ID, 
		Vote_Date = DATEADD(DAY, DATEDIFF(DAY, 0, Vote_Time), 0), 
		Vote_Cnt  = COUNT(*)
	FROM   	dbo.Votes 
	GROUP BY Voter_ID, DATEADD(DAY, DATEDIFF(DAY, 0, Vote_Time), 0)
) v
GROUP BY Voter_ID

[edit] C & P fault

KH
[spoiler]Time is always against us[/spoiler]

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 05:18:08

No Go

It says : Incorrect syntax near '='.

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 05:21:22

I tried changing it to this and it didn't like this either, said it didnt like vote_date.

SELECT Voter_ID, COUNT(*) AS Vote_Days, SUM(Vote_Cnt) AS Total_Votes
FROM
(
SELECT Voter_ID,
DATEADD(DAY, DATEDIFF(DAY, 0, Vote_Time), 0) AS Vote_Date,
COUNT(*) AS Vote_Cnt
FROM dbo.Votes
GROUP BY Voter_ID, DATEADD(DAY, DATEDIFF(DAY, 0, Vote_Time) , 0) AS vote_date
) v

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 05:25:52

updated the query in earlier post.

KH
[spoiler]Time is always against us[/spoiler]

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 05:32:25

Server: Msg 8118, Level 16, State 1, Line 1
Column 'v.Voter_ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

SELECT Voter_ID, Vote_Days = COUNT(*), Total_Votes = SUM(Vote_Cnt)
FROM
(
SELECT Voter_ID,
Vote_Date = DATEADD(DAY, DATEDIFF(DAY, 0, Vote_Time), 0),
Vote_Cnt = COUNT(*)
FROM dbo.Votes
GROUP BY Voter_ID, DATEADD(DAY, DATEDIFF(DAY, 0, Vote_Time), 0)
) v

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 05:34:42

sorry, updated that post again

KH
[spoiler]Time is always against us[/spoiler]

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 05:43:26

Works perfectly! Thanks so much for the help, I knew it would require a subselect but just couldn't get my brain around it this morning.

Thanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 05:51:49

Perhaps you can explain the little v at the end of the select.

Thanks again,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

pleitch
Starting Member

8 Posts

Posted - 2008-01-09 : 22:43:19

quote:
Originally posted by pwcphoto

Perhaps you can explain the little v at the end of the select.

A nested Select needs a name.

Select *
From
( Select 'hello' as a)

will not work. A simple alias makes it "come to life":
Select *
From
( Select 'hello' as a) as Goofunkle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources