selecting the COUNTs of each possible entry in a field?

[LucyP]

Hi

I have a table which contains a field that can be set to either a or b (it may be expanded in the future so it's not a bit field). I want to select the count of the occurances of each- eg. for this particular username there are 3 a's and 1 -in one SQL query b (currently I'm using 2). Can anyone help?

 

[cmmrfrds]

You should be able to do what you want with a subquery. I am doing something similar and here is an example from one of my stored procedures. The outer loop would select each value in the table and the inner loop counts the occurances of that value.

SELECT
(Select count(isnull(inAC.closedDate,0))
FROM dbo.AlarisCase inAC
INNER JOIN
dbo.Jurisdiction inJ ON
inAC.jurisdictionID = inJ.jurisdictionID
WHERE inJ.jurisdictionID = outJ.jurisdictionID
and inAC.closedDate is not null) as closeCount,

((Select count(isnull(inAC.alarisCaseID,0))
FROM dbo.AlarisCase inAC
INNER JOIN
dbo.Jurisdiction inJ ON
inAC.jurisdictionID = inJ.jurisdictionID
WHERE inJ.jurisdictionID = outJ.jurisdictionID )
-
(Select count(isnull(inAC.closedDate,0))
FROM dbo.AlarisCase inAC
INNER JOIN
dbo.Jurisdiction inJ ON
inAC.jurisdictionID = inJ.jurisdictionID
WHERE inJ.jurisdictionID = outJ.jurisdictionID
and inAC.closedDate is not null
) ) as openCount,

outJ.jurisdictionDesc as category
FROM dbo.AlarisCase outAC INNER JOIN
dbo.Jurisdiction outJ ON
outAC.jurisdictionID = outJ.jurisdictionID

GROUP BY outJ.jurisdictionDesc, outJ.jurisdictionID
ORDER BY outJ.jurisdictionDesc

 

[RickCole]

Without seeing the table structure I'm guessing a little here as to what you want but try something like this

select username count(field) as Total_count from tablename
where username = 'username'
group by username

Rick.

 

[tlbroadbent]

I would modify Rick's suggestion slightly.

Select
UserName,
FieldA,
Count(*) as Cnt
From TableName
Where UserName = 'username'
Group By UserName

This will give you a count of the contents of FiledA by user. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[LucyP]

thanks, but that only gives me a count of the records returned. I want a count of both the records with field1 set to a and then a count of the records with field1 set to be in 1 query. I think the subqueries response will do it, but I haven't quite unpicked the sql yet!

NB if anyone can make it clearer - my current sql is
SELECT COUNT(field1) as Cnt WHERE Username='username' AND field1='a' and then I have a second sql statement with the a changed to a b.

 

[tlbroadbent]

Here are two solutions for the price of one.

SELECT
CntA=
sum(Case When Field1='a' Then 1 Else 0 End),
CntB=
sum(Case When Field1='b' Then 1 Else 0 End)
FROM table1
WHERE Username='username'

SELECT
CntA=(SELECT COUNT(field1)
FROM table1
WHERE Username='username'
AND field1='a'),
CntB=(SELECT COUNT(field1)
FROM table1
WHERE Username='username'
AND field1='b')

Will one of these meet your need? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

The inner select counts the occurs of field1 for the Username. If you leave user name off, then it will count field1 for each Username. You need to put an alias on the table to differentiate between the inner and outer reference to the same table. I am using SQL Server so I hope the syntax is the same in an MDB.

SELECT Username,
(SELECT COUNT(field1)
FROM TableX inX
Where inX.Username = outX.Username) as cnt
FROM TableX outX
WHERE Username='username'
GROUP BY Username