Using COUNT() function

# Thread: Using COUNT() function

1. Member
Join Date
Dec 1969
Posts
78

## Using COUNT() function

Hi folks:<BR><BR>I have a table with two columns YEAR, EVENT. I&#039;m trying to run statistics on the table to determine how many arrivals and departures occur each year, and a running total. The problem is I can&#039;t seem to get the query to work. Here&#039;s my SQL code...<BR><BR>SELECT Count([temptable.Event]="ARRIVED") AS MyArrivalCount, Count([temptable.Event]="DEPARTED") AS MyDepartureCount,temptable.Year, temptable.Event<BR>FROM temptable<BR>GROUP BY temptable.Year, temptable.Event;<BR><BR>and the problem is that the code doesn&#039;t work. It runs as if the expression in the COUNT function doesn&#039;t even exist and it counts all the events that occurred in that year, regardless of what value the event has. Any idea how I can accomplish this seemingly simple task?<BR><BR>Thanks!!!!<BR><BR>Sean M.

2. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: Using COUNT() function

select t.year,t.event,(select count(*) from temptable t2<BR>where t2.event = &#039;ARRIVED&#039; and t2.year = t.year) as ARRIVED,<BR>,(select count(*) from temptable t3<BR>where t3.event = &#039;DEPARTED&#039; and t3.year = t.year) as DEPARTED from temptable t <BR>group by t.year,t.event

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Query makes no sense!

(1) Your expression inside the COUNT returns a true or false, yes. But then you are asking COUNT to count the number of...ready for this?...BOOLEAN results that it sees.<BR><BR>But since both true and false are boolean results...it returns the count of all records!<BR><BR>(2) Since you have EVENT in your list of selected fields, and since you then GROUP BY EVENT, you will get (at least) *two* records. One for each kind of event.<BR><BR>************<BR><BR>If you *want* to get multiple records per year, just do:<BR><BR>SELECT Count(*) AS MyCount, [Year], [Event]<BR>FROM temptable<BR>WHERE [Event] IN (&#039;ARRIVED&#039;,&#039;DEPARTED&#039;)<BR>GROU P BY [Year], [Event];<BR><BR>*************<BR><BR>If you want only *one* record per year...<BR><BR>You didn&#039;t say what DB this is, but you should be able to do this:<BR><BR>SELECT SUM( CASE WHEN [Event]="ARRIVED" THEN 1 ELSE 0 END ) AS MyArrivalCount, <BR>&nbsp; &nbsp; SUM( CASE WHEN [Event]="DEPARTED" THEN 1 ELSE 0 END ) AS MyDepartureCount,<BR>&nbsp; &nbsp; [Year]<BR>FROM temptable<BR>GROUP BY [Year]<BR>ORDER BY [Year]<BR><BR>That&#039;s SQL Server. If you are using the right driver, it might work for Access. But if not, try:<BR><BR>SELECT SUM( IIF([Event]="ARRIVED",1,0) ) AS MyArrivalCount, <BR>&nbsp; &nbsp; SUM( IIF([Event]="DEPARTED",1,0) ) AS MyDepartureCount,<BR>&nbsp; &nbsp; [Year]<BR>FROM temptable<BR>GROUP BY [Year]<BR>ORDER BY [Year]<BR><BR>************************<BR><BR>I put [...] around Year because that&#039;s a keyword in both Access and SQL Server. Since you put them around temptable.Event, I put them around Event. Is it a keyword, too? Ehhh...probably.<BR><BR>I yanked the table name out of your field selectors; you don&#039;t need it when there is only one table, and it just makes the code more confusing IMHO.<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Does this make sense to you???

So as I read that, if you had data such as this in your table:<BR><BR>year -- event <BR>2001 -- ARRIVED<BR>2001 -- DEPARTED<BR>2001 -- DEPARTED<BR>2001 -- DEPARTED<BR>2001 -- WAFFLED<BR>2002 -- ARRIVED<BR>2002 -- ARRIVED<BR>2002 -- TRESPASSED<BR>You would get these results out:<BR><BR>year, event, Arrived, Departed<BR>2001, ARRIVED, 1, 3<BR>2001, DEPARTED, 1, 3<BR>2001, WAFFLED, 1, 3<BR>2002, ARRIVED, 2, 0<BR>2002, TRESPASSED, 2, 0<BR><BR>WHAT IS THE POINT of grouping by Event and then getting the counts for *both* Arrived and Departed for *EACH* event!??<BR><BR>It seems to me like EVENT should *not* be in the SELECT list (or in the GROUP BY, of course).<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
2,437

## No I got lost somewhere ;) <eop>

.

6. Senior Member
Join Date
Dec 1969
Posts
2,437

## This should be ok though.

select t.year,(select count(*) from temptable t2 <BR>where t2.event = &#039;ARRIVED&#039; and t2.year = t.year) as ARRIVED, <BR>,(select count(*) from temptable t3 <BR>where t3.event = &#039;DEPARTED&#039; and t3.year = t.year) as DEPARTED from temptable t <BR>group by t.year

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Yep, that's what I thought...

See my alternative to that using SUM( CASE WHEN... )<BR><BR>Any idea which is faster?<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: Yep, that's what I thought...

Unless the query optimizer is really good I would assume that your version is quicker.<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well, that was a given...

&#062; Unless the query optimizer is really good <BR><BR>I don&#039;t think I care about crappy optimizers. &#060;grin /&#062;<BR><BR>

10. Member
Join Date
Dec 1969
Posts
78

## RE: Query makes no sense!

Wow. Well, the bottom line is that the code works GREAT!!!! And I had no idea you could do IF/CASE statements!!!! It&#039;s amazing what you can learn if you just watch what those who know do the same problem.<BR><BR>You guys are really great!<BR><BR>Thanks again :)<BR><BR>Sean

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•