SQL - Count ?

[SpookiePower]

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
....
....

 

[Jeff Boyce]

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.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

 

[John Spencer]

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


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

 

[SpookiePower]

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