SQL Select Query With Count And Where Statement

Question

I always seem to trip myself up during these types of SQL Statements. Here is what I'm attempting to accomplish.

My Example Table

Name    Date    Type
Bob     9/28/11 1
Bob     9/27/11 1
Bob     9/28/11 2
Debra   9/28/11 1

I'm trying to write a SQL Statement that would give me all the names, their total count occured, and then a Date Filter. I'll write a rought statement with completely wrong syntax, but I think it'll convey what I'm attempting to do...I think.

SELECT Name, Count(*) As Total
FROM Table
GROUP BY Name, Total
WHERE Date = Today (I'm aware you can't do a WHERE in a GROUP BY) AND Type = 1

Essentially, I would like to get back a set of data that would show the Name, how many instances of Type 1 for today.

I don't think I'm searching for the proper question to actually be able to effectively research this on my own as well, probably because I'm wording it improperly.

Any help would be appreciated!

@AbeMiessler It would be a simple resultset of the name with the total count for that name, and then to the next name, etc. I do believe it was a simple syntax error, and I just didn't see it. The folks below explained that the WHERE comes before the GROUP BY and for some jacked up reason....it just didn't click for me. I've been looking at code all day...fatigue. — RogueSpear00
– RogueSpear00, Commented Sep 28, 2011 at 21:00

Matthew Farwell · Accepted Answer · 2011-09-28 20:54:52Z

3

Your WHERE was in the wrong place:

SELECT Name, Count(*) As Total
FROM Table
WHERE Date = Today AND Type = 1
GROUP BY Name

answered Sep 28, 2011 at 20:54

Matthew Farwell

61.8k18 gold badges134 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

p.campbell · Accepted Answer · 2011-09-28 20:54:41Z

2

You just need to move your WHERE after your FROM.

SELECT Name, Count(*) As Total
FROM Table
WHERE [Date] = CAST(getdate() as Date) -- or 'Jan 1 1990'
AND [Type] = 1
GROUP BY Name

answered Sep 28, 2011 at 20:54

p.campbell

101k70 gold badges264 silver badges326 bronze badges

1 Comment

Magnus Over a year ago

+1 possibly need to cast the Date field also (if it is a DateTime)

SQLMenace · Accepted Answer · 2011-09-28 20:56:02Z

2

WHERE comes before GROUP

SELECT Name, Count(*) As Total
FROM Table
WHERE Date >= @Today --assuming your date passed in has no time
AND Date < DATEADD(d,1,@Today) 
AND Type = 1
GROUP BY Name, Total

example you can run

SELECT LEFT(name,1) FROM sysobjects
WHERE name < 'p'
GROUP BY LEFT(name,1)

answered Sep 28, 2011 at 20:56

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Collectives™ on Stack Overflow

SQL Select Query With Count And Where Statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related