SQL - Count ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SpookiePower - 15 Nov 2007 10:23 GMT

I have a table called TReport which have to
columns iTeam1 and iTeam2.

I have 3 teams, 1 2 and 3 where each of them have a name
which I get from the table TTeam.id

I want to count how many times each team are in
iTeam1 and iTeam2 and them sort them by how
many times they are in these two column, with each
teams name.

My first problem is how to count two columns.
I have tried to do it like this -

count(iTeam1+iTeam2) and Sum(iTeam1 +iTeam2)
But it does not work.

This is my SQL -

SELECT Holdnavn, count(iTeam1+iTeam2) AS X
FROM TReport INNER JOIN TTeam ON TReport.iTeam1=TTeam.id
WHERE iTeam1=11 Or iTeam1=12 Or iTeam1=13 iTeam2=11 Or iTeam2=12 Or
iTeam2=13
GROUP BY Holdnavn
ORDER BY count(iTeam1+iTeam2) DESC;

My final result should look like this.

BlueTeam    100
ReadTeam    70
GreenTeam    50
...
...

Reply to this Message

Jeff Boyce - 15 Nov 2007 13:05 GMT

If you are saying that you have a table with two columns, either of which
could hold a team name/ID, then consider revisiting the structure -- it may
need further normalization.

If you are absolutely, positively, without-a-doubt certain that your
situation will never, ever, EVER require more that two "teams", then you
might not need to look into setting up a one-to-many new table.

If you create one query that lists all teams found in [iTeam1], another
query that lists all teams found in [iTeam2], and then a UNION query that
lists all teams found (in both), you can then create a Totals query that is
based on the UNION query and uses GroupBy and Count to get what you're
describing.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> I have a table called TReport which have to
> columns iTeam1 and iTeam2.
[quoted text clipped - 29 lines]
> ...
> ...

Reply to this Message

John Spencer - 15 Nov 2007 13:28 GMT

One option would be to build a UNION ALL query and then use that as the
source of your aggregate (totals) query.

Select Hodnavn, Iteam1 as iTeam
FROM TReport INNER JOIN TTeam ON TReport.iTeam1=TTeam.id
WHERE iTeam1 in (11,12 ,13)
UNION ALL
Select Hodnavn, Iteam2
FROM TReport INNER JOIN TTeam ON TReport.iTeam2=TTeam.id
WHERE iTeam2 in (11,12 ,13)

Now you can use that
SELECT Hodnavn, iTeam, Count(iTeam) as Frequency
FROM SavedUnionQuery
GROUP BY HodNavn, iTeam

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a table called TReport which have to
> columns iTeam1 and iTeam2.
[quoted text clipped - 29 lines]
> ...
> ...

Reply to this Message

SpookiePower - 15 Nov 2007 14:30 GMT

Thanks both of you.
I'll try it later to day :)

Reply to this Message