count multi fields in one quiery

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

Other Forums

MS Access

count multi fields in one quiery

Author

Topic

Naief
Starting Member

2 Posts

Posted - 2004-06-23 : 02:34:27

Image a database table named DBTABLE with a field called MULT_CHOICE (let's say there's another field that contains a student ID--pretend the query I need summarizes how a student answered a 100-question multiple choice exam). The MULT_CHOICE field contains either A, B, C, or D. I want to use the count() function and have my output to look like this:

STUDENT ID------COUNT(A) --------COUNT(B) -------- COUNT(C) --------COUNT(D)
----------------------------------------------------------------------------------------
11 ----------------- 32 ---------------- 11 ----------------- 19 ---------------- 38

Is this possible? I've done it using four select statements, all unioned together, but that obviously produces:

STUDENT ID---------- COUNT(col)
--------------------------------------
11 ---------------------- 32
11 ---------------------- 11
11 ---------------------- 19
11 ---------------------- 38

I'd prefer to have something that looks more like the first resultset. The problem I'm having is that I don't know enough about SQL to know if it's possible or not. Since I differentiate between MULT_CHOICE in the WHERE clause, I don't know how (or if it's possible) to associate that with separate columns. Namely:

SELECT STUDENT_ID, COUNT(MULT_CHOICE) as colA, COUNT(MULT_CHOICE) as colB,
COUNT(MULT_CHOICE) as colC, COUNT(MULT_CHOICE) as colD
FROM DBTABLE
WHERE MULT_CHOICE = 'A' // for colA
and MULT_CHOICE = 'B' // for colB
and MULT_CHOICE = 'C' // for colC
and MULT_CHOICE = 'D' // for colD

If anyone can help me out, it's greatly appreciated.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-23 : 02:40:55

Access's CrossTab queries will do exactly what you need.

It even has a nice wizard to help you.

Naief
Starting Member

2 Posts

Posted - 2004-06-23 : 03:47:16

yes it is really helps

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources