Trying to Iff Statement...Cant get it to work in Querry

[Guest]

Following SQL statement:

SELECT [All Conferences Union].[Home Team Results], Count([All Conferences
Union].[Home Team Results]) AS [CountOfHome Team Results], [All Conferences
Union].[Home Team Name]
FROM [All Conferences Union]
GROUP BY [All Conferences Union].[Home Team Results], [All Conferences
Union].[Home Team Name]
HAVING ((([All Conferences Union].[Home Team Results])="loss"))
ORDER BY [All Conferences Union].[Home Team Results] DESC , Count([All
Conferences Union].[Home Team Results]) DESC , [All Conferences Union].[Home
Team Name];

I want to add an IIF statement that if no team has a loss in the home team
results then put a 0 in the column.

For example right now, Ohio State, rutgers and 4 other teams have no losses,
but I would still like them to show up as 0 losses.

Thanks, hopefully this could be done. the IIF examples I have are based on
2 condidtions.

 

[Guest]

As you are aggregating by the Home Team Results column you don't need to
GROUP BY that column, just by the team name. To conditionally count the rows
on the basis of the result being "loss" SUM an expression which returns 1 or
0, using the IIF function. The sum is the same as a count of the "loss"
values as it is a sum of the returned ones. If no rows for a team have a
"loss" value then the sum of the returned values, all zeros, is zero.

Try this:

SELECT
[Home Team Name],
SUM(IIF([[Home Team Results] = "loss", 1, 0)) AS Losses
FROM [All Conferences Union]
GROUP BY [Home Team Name]
ORDER BY SUM(IIF([[Home Team Results] = "loss", 1, 0)) DESC;

Ken Sheridan
Stafford, England

 

[Guest]

It worked Great. Just had to remove the extra bracket from:

SUM(IIF([[Home Team Results]

Also, great clarrification of your code, I actually understand it VS. just
using it.

As you are aggregating by the Home Team Results column you don't need to
GROUP BY that column, just by the team name. To conditionally count the rows
on the basis of the result being "loss" SUM an expression which returns 1 or
0, using the IIF function. The sum is the same as a count of the "loss"
values as it is a sum of the returned ones. If no rows for a team have a
"loss" value then the sum of the returned values, all zeros, is zero.

Try this:

SELECT
[Home Team Name],
SUM(IIF([[Home Team Results] = "loss", 1, 0)) AS Losses
FROM [All Conferences Union]
GROUP BY [Home Team Name]
ORDER BY SUM(IIF([[Home Team Results] = "loss", 1, 0)) DESC;

Ken Sheridan
Stafford, England

Following SQL statement:

SELECT [All Conferences Union].[Home Team Results], Count([All Conferences
Union].[Home Team Results]) AS [CountOfHome Team Results], [All Conferences
Union].[Home Team Name]
FROM [All Conferences Union]
GROUP BY [All Conferences Union].[Home Team Results], [All Conferences
Union].[Home Team Name]
HAVING ((([All Conferences Union].[Home Team Results])="loss"))
ORDER BY [All Conferences Union].[Home Team Results] DESC , Count([All
Conferences Union].[Home Team Results]) DESC , [All Conferences Union].[Home
Team Name];

I want to add an IIF statement that if no team has a loss in the home team
results then put a 0 in the column.

For example right now, Ohio State, rutgers and 4 other teams have no losses,
but I would still like them to show up as 0 losses.

Thanks, hopefully this could be done. the IIF examples I have are based on
2 condidtions.

 

[Guest]

what does the DESC; do at the end of the sql statement

 

[John Vinson]

what does the DESC; do at the end of the sql statement

It's part of an ORDER BY clause and it means to sort in DESCending
order.

For example, if your SQL (which, by the way, should perhaps have been
posted) has

ORDER BY LastName;

or

ORDER BY LastName ASC;

the query will sort records in alphabetical order by last name. If it
says

ORDER BY LastName DESC;

it will sort in reverse alphabetical order, Zybrowski first, Aarons
last.

John W. Vinson[MVP]

 

[Guest]

Thanks, I need to start looking at the SQL statements after I drag fields to
the querry field. Sorry about the semi dumb question.