Return a count of 0

[max]

I am beginning to learn sql and need some help.

I have a table of customers with their addresses. Let's say I want to
run a query returning the number of customers whose last name is
"Smith" by state.

If I use the following:

SELECT customers.state , Count(*) AS [Count Last Name]
FROM customers
WHERE (((customers.la st_name)="Smith "))
GROUP BY customers.state ;

it returns almost what I want. If there are no "Smiths" in a
particular state, then that state is ommitted in my query result. I
would like to return that state with a count field value of 0 if a
particular name doesn't exist in that state. Do I need to use some
kind of conditional expression to accomplish this? Thanks for any
ideas.

max

[salad]

max wrote:

I am beginning to learn sql and need some help.

I have a table of customers with their addresses. Let's say I want to
run a query returning the number of customers whose last name is
"Smith" by state.

If I use the following:

SELECT customers.state , Count(*) AS [Count Last Name]
FROM customers
WHERE (((customers.la st_name)="Smith "))
GROUP BY customers.state ;

it returns almost what I want. If there are no "Smiths" in a
particular state, then that state is ommitted in my query result. I
would like to return that state with a count field value of 0 if a
particular name doesn't exist in that state. Do I need to use some
kind of conditional expression to accomplish this? Thanks for any
ideas.

max

You'll need a StateList table to be added to the query. In the query
builder add both tables. Click on the relationship line and select the
option for All records In StatesList and those that match in Customers.
You'll also need to group on the StatesList state name, not the state
in the customers file

[max]

salad wrote:

max wrote:

I am beginning to learn sql and need some help.

I have a table of customers with their addresses. Let's say I want to
run a query returning the number of customers whose last name is
"Smith" by state.

If I use the following:

SELECT customers.state , Count(*) AS [Count Last Name]
FROM customers
WHERE (((customers.la st_name)="Smith "))
GROUP BY customers.state ;

it returns almost what I want. If there are no "Smiths" in a
particular state, then that state is ommitted in my query result. I
would like to return that state with a count field value of 0 if a
particular name doesn't exist in that state. Do I need to use some
kind of conditional expression to accomplish this? Thanks for any
ideas.

max

You'll need a StateList table to be added to the query. In the query
builder add both tables. Click on the relationship line and select the
option for All records In StatesList and those that match in Customers.
You'll also need to group on the StatesList state name, not the state
in the customers file

I tried to do what you recommended. But it is still not returning the
results I want. Here is the sql statement that was generated

SELECT [StateList].state, Count(*) AS [Count Last Name]
FROM [StateList] LEFT JOIN customers ON [StateList].state =
customers.state
WHERE (((customers.la st_name)="Smith "))
GROUP BY [StateList].state;

Am I doing something wrong? Thanks

[salad]

max wrote:

salad wrote:

>>max wrote:

>>>I am beginning to learn sql and need some help.

I have a table of customers with their addresses. Let's say I want to
run a query returning the number of customers whose last name is
"Smith" by state.

If I use the following:

SELECT customers.state , Count(*) AS [Count Last Name]
FROM customers
WHERE (((customers.la st_name)="Smith "))
GROUP BY customers.state ;

it returns almost what I want. If there are no "Smiths" in a
particular state, then that state is ommitted in my query result. I
would like to return that state with a count field value of 0 if a
particular name doesn't exist in that state. Do I need to use some
kind of conditional expression to accomplish this? Thanks for any
ideas.

max

You'll need a StateList table to be added to the query. In the query
builder add both tables. Click on the relationship line and select the
option for All records In StatesList and those that match in Customers.
You'll also need to group on the StatesList state name, not the state
in the customers file

I tried to do what you recommended. But it is still not returning the
results I want. Here is the sql statement that was generated

SELECT [StateList].state, Count(*) AS [Count Last Name]
FROM [StateList] LEFT JOIN customers ON [StateList].state =
customers.state
WHERE (((customers.la st_name)="Smith "))
GROUP BY [StateList].state;

Am I doing something wrong? Thanks

Nope. I gave a pointer but not the ideal. There are times when Count
won't work. In this case we were filtering on the lastname being Smith.
And that filtered most of your records out. What we needed to do was
sum...if it's Smith, add 1, else add 0.

SELECT StateList.State , Sum(IIf([LastName]="Smith",1,0 )) AS Expr1
FROM StateList LEFT JOIN Customers ON StateList.State = Customers.State
GROUP BY StateList.State ;

See if this works.

[max]

salad wrote:

max wrote:

salad wrote:

>max wrote:

I am beginning to learn sql and need some help.

I have a table of customers with their addresses. Let's say I want to
run a query returning the number of customers whose last name is
"Smith" by state.

If I use the following:

SELECT customers.state , Count(*) AS [Count Last Name]
FROM customers
WHERE (((customers.la st_name)="Smith "))
GROUP BY customers.state ;

it returns almost what I want. If there are no "Smiths" in a
particular state, then that state is ommitted in my query result. I
would like to return that state with a count field value of 0 if a
particular name doesn't exist in that state. Do I need to use some
kind of conditional expression to accomplish this? Thanks for any
ideas.

max
You'll need a StateList table to be added to the query. In the query
builder add both tables. Click on the relationship line and select the
option for All records In StatesList and those that match in Customers.
You'll also need to group on the StatesList state name, not the state
in the customers file

I tried to do what you recommended. But it is still not returning the
results I want. Here is the sql statement that was generated

SELECT [StateList].state, Count(*) AS [Count Last Name]
FROM [StateList] LEFT JOIN customers ON [StateList].state =
customers.state
WHERE (((customers.la st_name)="Smith "))
GROUP BY [StateList].state;

Am I doing something wrong? Thanks

Nope. I gave a pointer but not the ideal. There are times when Count
won't work. In this case we were filtering on the lastname being Smith.
And that filtered most of your records out. What we needed to do was
sum...if it's Smith, add 1, else add 0.

SELECT StateList.State , Sum(IIf([LastName]="Smith",1,0 )) AS Expr1
FROM StateList LEFT JOIN Customers ON StateList.State = Customers.State
GROUP BY StateList.State ;

See if this works.

Yes. That did the trick. Thanks so much.