Jump to content

Help with this query


jalexm

Recommended Posts

Hello,

I have a table similar to this:

Table: Sectors
Sector Type
--------------
A X
B X
A Y
C Z
A X
B Z

And I need to build a query so that I get these results:

Sector Nr of X Nr of Y Nr of Z
--------------------------------------------
A 2 1 0
B 1 0 1
C 0 0 1

Does anyone know how to get this?
Note: using an old MS-Access version (2003).

Thanks.


Link to comment
Share on other sites

You could do a count query where you group by both columns, but it's not going to return the data formatted like that. It would return a record set like this, but you could use another language to loop through it and format the table:

 

A   X   2
A   Y   1
B   X   1
B   Z   1
C   Z   1
You can get that result with a count query where you group by both columns.
Link to comment
Share on other sites

  • 4 weeks later...

Try something like this...

 

select Sector,

(select count(type) from sectors b where a.sector = b.sector and type = 'X') AS "Nr of X",

(select count(type) from sectors b where a.sector = b.sector and type = 'Y') AS "Nr of Y",

(select count(type) from sectors b where a.sector = b.sector and type = 'Z') AS "Nr of X"

from Sectors a

group by sector

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...