MS Access SQL question

[StillFlash]

I'm in learning and testing phase of loading Retrosheet event files into Access.

Looking for SQL code which will tell me how many times each value occurs in a specified field. For example, daynight field has value either 'day' or 'night' - I want the query to return how many times the value was 'day' and how many times was 'night'.

(Day and night used for simple example, mostly will be used for how many singles, doubles, triples, homeruns based on value of hitcode)

[SABR Matt]

So...you want to count how many times a game was played in the day vs. how many at night?* I'm confused as to what you mean by "value"

[weskelton]

StillFlash,

This should probably fit your needs...

select hitcode, count(*)
from event_log
group by hitcode;

[Tango Tiger]

In Access, you use the
- QUERY tab,
- stay in DESIGN view,
- show the table you want,
- drag the fields that interest you and
- then, most important, you show the totals line (select the icon that says Totals when you hover over it)
- under the field you want to add on, you will see the "Totals" line, and you select GROUP BY
- under the field you want to count, you select SUM or COUNT or whatever you want to do

[Tango Tiger]

And if you select SQL mode, you will end up with exactly what wes just showed in post 3.

[StillFlash]

value is the piece of data that occupies andy given field

In the hitvalue field produced by bevent, possible values range from 0-4

(Right now, I want to do park effects split into rhb & lhb for 2006 & 2007, which are not yet in the KJOK database)

In a test query, I only select two fields, Name and Result

If I group by Name and count Results, it tells me how many total Results there are for each Name...

What I want to know is how many of each possible value of Result, grouped by Name...there's only one field I'm counting (hitvalue for example), but I want to output a seperate column for each distinct value found in that field.

I had googled a tutorial that showed how to use CASE to test each value, then increment a new variable, then these variables become the columns...but I found out CASE is not supported in MS Access...when I tried IF instead, I kept getting a syntax error which I haven't figured out yet.

I've been using Access for about a year and a half, and wanted to give it a try, but I may have to just go over to MySQL...I installed it this past weekend on my home computer, and have a book, but haven't played with it yet.

Thanks for the help so far, hope I can still get this resolved

[weskelton]

So now it looks like you're trying to get a total for each type of Result (hitscode) grouped by name. If I'm understanding this right, then you'd want...

select name, hitcode, count(*)
from event_log
group by name, hitcode;

This should give you something like...

name hitcode count(*)
--------- -------- ---------
Adams 1b 100
Adams 2b 40
Adams 3b 3
Adams HR 20
Jones 1b 50
Jones 2b 10
Jones 3b 1
Jones HR 5

etc...

Of course it'll be a much nicer layout.

[StillFlash]

Please don't think I'm picky, but I'm looking for a more standard layout such as
..............1b....2b...3b....hr
Adams 100 40 3 20
Jones 50 10 1 5

That way I can run bevent, write the queries, and get a standard stat style output

[StillFlash]

OK - I got it! Turns out Access requires IIF instead of IF, try finding that in the Help!

SELECT name,
SUM(IIF([hitvalue]='1',1,0)) AS SI,
SUM(IIF([hitvalue]='2',1,0)) AS DO,
SUM(IIF([hitvalue]='3',1,0)) AS TR,
SUM(IIF([hitvalue]='4',1,0)) AS HR
FROM event
GROUP BY name;

Put in a SUM statement for each potential value you want to count. When IIF is True (a match), the value is set at 1, otherwise 0, which causes SUM to increment SI, DO, TR & HR by 1 each time a match is found

[weskelton]

I guess I should have figured that was what you were ultimately after. FWIW, I have created a view in MySQL with the "if" translations as you have done here. You could do the same in Access by creating a base Query. I then treat that view/query as the base for all of my other aggregate queries. This way you only need to do the cloumn translations once and the subsequent queries look more like convetional SQL, i.e.

select name, sum(HR)
from bat_stat_by_event
group by name;

[Tango Tiger]

In Access, as in most of DB, you don't need the "IF" or "CASE".

In this case, you can do:

select player, -sum(hitvalue=4) as HR
group by player

hitvalue=4 will evaluate as 0 (false) or -1 (true) in Access.

You'd have to play around other DBs to see how they handle it.