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

Select Distinct Question

P: 52
Hello All,

I'm building an inventory database for my company and I need to setup some specific select statements to write the material to a web page. There are duplicate descriptions in the database that we don't need to display on the web page and I need to total the quantity even though it doesn't display every description. So I'm trying to use a "Select Distinct". Problem is I can't get it quite right. What I have so far is below. Any help would be great.

Expand|Select|Wrap|Line Numbers
  1. $sql="SELECT DISTINCT controlnumber, SUM(quantity), description, location FROM tablename where id < '9999' ";
  2.  
The error I receive is...

Expand|Select|Wrap|Line Numbers
  1. You tried to execute a query that does not include the specified expression 'controlnumber' as part of an aggregate function.
  2.  
So basically in the database we have the following...

1 blue chair
1 blue chair
1 blue chair
2 blue chair

I need it to display on the web page as follows...

5 blue chair

Any ideas?

Thanks
Dec 19 '07 #1
Share this Question
Share on Google+
5 Replies


nathj
Expert 100+
P: 938
Hello All,

I'm building an inventory database for my company and I need to setup some specific select statements to write the material to a web page. There are duplicate descriptions in the database that we don't need to display on the web page and I need to total the quantity even though it doesn't display every description. So I'm trying to use a &quot;Select Distinct&quot;. Problem is I can't get it quite right. What I have so far is below. Any help would be great.

Expand|Select|Wrap|Line Numbers
  1. $sql=&quot;SELECT DISTINCT controlnumber, SUM(quantity), description, location FROM tablename where id < '9999' &quot;;
  2.  
The error I receive is...

Expand|Select|Wrap|Line Numbers
  1. You tried to execute a query that does not include the specified expression 'controlnumber' as part of an aggregate function.
  2.  
So basically in the database we have the following...

1 blue chair
1 blue chair
1 blue chair
2 blue chair

I need it to display on the web page as follows...

5 blue chair

Any ideas?

Thanks
Hi,

Depending on the field type of description this may or may not work, but you could try something like:

[code]
$sql=&quot;SELECT count(description) as total, description FROM tablename where id < '9999' group by description &quot;;

[/code[]
This should work for you. The result should be two columns - total and description which should have 5 and blue chair respectively.

Cheers
nathj
Dec 19 '07 #2

P: 52
Ok, so now I have this as the select...

Expand|Select|Wrap|Line Numbers
  1. $sql="SELECT count(quantity) as total, description FROM tablename where id < '9999' group by description";
  2.  
And here is the error I get...

Expand|Select|Wrap|Line Numbers
  1. SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
  2.  
The quantity field in the access database is number type and is specified as an integer. In addition every record has quantity value from 0 and up. The description field is text type.
Dec 19 '07 #3

nathj
Expert 100+
P: 938
Ok, so now I have this as the select...

Expand|Select|Wrap|Line Numbers
  1. $sql="SELECT count(quantity) as total, description FROM tablename where id < '9999' group by description";
  2.  
And here is the error I get...

Expand|Select|Wrap|Line Numbers
  1. SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
  2.  
The quantity field in the access database is number type and is specified as an integer. In addition every record has quantity value from 0 and up. The description field is text type.
Hi,

At a guess, and without knowing your data structure, I'd say it's because you have the ID value you are teting against as a string. I would normally expect an ID to be an integer. So try the following:
Expand|Select|Wrap|Line Numbers
  1. SELECT count(quantity) as total, description FROM tablename where id < 9999 group by description
  2.  
Cheers
nathj
Dec 20 '07 #4

P: 52
Yes, your right and I figured it out yesterday. The select I ended up with (with a couple of extras) is...
Expand|Select|Wrap|Line Numbers
  1. $sql="SELECT Distinct SUM(quantity) as total,description,location FROM tablename where id < 9999 group by description,quantity,location order by description";
  2.  
Thanks for the guidance. Since I am new to PHP and so I don't have to start a new topic do you know know to extract the year from a date string? For instance I have a field in the table called datepurchased that has "12/12/2007" in it and when I call the records on the page all I want from the column is the year. Can I do something like year(odbc_result(variablename,"datepuchased")) to grab just the year.

Thanks again for the help
Dec 20 '07 #5

nathj
Expert 100+
P: 938
Yes, your right and I figured it out yesterday. The select I ended up with (with a couple of extras) is...
Expand|Select|Wrap|Line Numbers
  1. $sql="SELECT Distinct SUM(quantity) as total,description,location FROM tablename where id < 9999 group by description,quantity,location order by description";
  2.  
Thanks for the guidance. Since I am new to PHP and so I don't have to start a new topic do you know know to extract the year from a date string? For instance I have a field in the table called datepurchased that has "12/12/2007" in it and when I call the records on the page all I want from the column is the year. Can I do something like year(odbc_result(variablename,"datepuchased")) to grab just the year.

Thanks again for the help
Hi,

I'm glad you go tit sorted. The solution to the year thing is to do it in the SQL. So if I had a table of products with the following in ID, name, launchdate and I wanted to show the yaer a product was launched I wuld use the following SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT a.ID, a.name, year(a.launchdate) as launchYear FROM tbl_product
  2.  
You can then use the field lanuchYear when you handle the results.

Check out the MySQL Date Functions

Cheers
nathj
Dec 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.