multiple count values needed

[betheball]

I have the following query:

SELECT [Team], Count(*) AS CountOfP
FROM Monday
WHERE Field4="P"
GROUP BY [Team];

Then I have this one:

SELECT [Team], Count(*) AS CountOfT
FROM Monday
WHERE Field4="T"
GROUP BY [Team];

The only difference is one counts the occurrences of "P" and the other counts the occurences of "T". Is there a way to combine the two queries into one? I know I have seen more than one SELECT statement in the same query, but do not know if it can be done here or what the syntax would be.

 

[dcx693]

SELECT [Team], Count(*) AS CountOfPOrT
FROM Monday
WHERE Field4="P" Or Field4="T"
GROUP BY [Team];

 

[betheball]

Sorry, poor wording on my part. I need two separate values. I need the total Ps in one column and the total Ts in the next. I believe your solution will just give me the total of records that are either P o T. Right?

 

[RayH]

[Query]
SELECT DISTINCT (select count(Team) from Monday where Team="P") AS Pcount, (select count(Team) from Monday where Team="T") AS Tcount
FROM Monday;
[/Query]

 

[betheball]

Still not quite what I need. When the query executes, I need a three-column table. The first column will list the teams, the second the number of Ps for each team in the table column being evaluated and the third column the number of Ts from the same column. It will look like this:

 

[RayH]

Whoops! Totally misread that one.
I'll work on it. Not as easy as I first thought.

 

[Rich]

Use a CrosstabQuery

 

[RayH]

Damn, You just beat me to it. Was just about to post the same thing.
Good 'ol Rich

 

[betheball]

Can you give me an example? Sadly, I can say I have heard of a crosstab query, but not sure I have ever seen one? Also, is this type of query unique to Access, or will it travel well over to ASP? Appreciate all the assistance.

 

[RayH]

TRANSFORM Count([Monday].[id]) AS CountOfid
SELECT [Monday].[Team]
FROM Monday
GROUP BY [Monday].[Team]
PIVOT [Monday].[Field4];

 

[RayH]

Don't know about the uniqueness of Crosstabs to Access though or anything about ASP.
Not much help am I

 

[EMP]

If the crosstab query does not work in ASP, try this:-

SELECT Monday.Team, Abs(Sum([Field4]="P")) AS [Count Of P],
Abs(Sum([Field4]="T")) AS [Count Of T]
FROM Monday
GROUP BY Monday.Team;

 

[RayH]

Nice job!!

 

[betheball]

As my 13-year old would say, "You guys rock!" Most of the database stuff I do is to run my ASP pages. I frequent a FrontPage forum for most everything, but whenever I get stuck on a query, I know I can get the answer here. Thanks.

FYI - I used EMP's example for my ASP page. I will try Ray's when I get back to work because, sadly, I don't have Access at home.

Wow! I became a Senior Member. That sure implies alot of things that aren't true.

 

[betheball]

Curiosity question. If I use EMP's example without "ABS" the values all show as negative numbers. Why is that?

 

[EMP]

Numerically, True is -1. False is 0.

So Sum([Field4]="P") is actually summing -1s and 0s. The result is therefore negative.

Abs() converts the result to an absolute number.