By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,871 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,871 IT Pros & Developers. It's quick & easy.

Return a count of 0

P: n/a
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.last_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

Oct 17 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.last_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
Oct 17 '06 #2

P: n/a
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.last_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.last_name)="Smith"))
GROUP BY [StateList].state;

Am I doing something wrong? Thanks

Oct 18 '06 #3

P: n/a
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.last_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.last_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.
Oct 18 '06 #4

P: n/a
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.last_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.last_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.

Oct 18 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.