MySQL ODBC Connector - query help?

Author

query help?

Richard Reina

2006-02-25, 9:43 am

--0-1825428838-1140713282=:14188
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number.

|ID | vendor_no | date |
|2354 | 578 | "2005-12-23"|
|2355 | 334 | "2005-12-24"|
|2356 | 339 | "2005-12-26"|
|2357 | 339 | "2005-12-26"|
|2358 | 339 | "2005-12-26"|
|2359 | 445 | "2005-12-26"|
|2354 | 522 | "2005-12-27"|
|2355 | 522 | "2005-12-27"|

Would I use select count? Any help would be greatly appreciated.

A people that values its privileges above its principles soon loses both.
-Dwight D. Eisenhower.
--0-1825428838-1140713282=:14188--

cnelson@nycap.rr.com

2006-02-25, 9:43 am

> I am a novice when it come to queries such as this and was hoping
> someone could help me write a query that tells me how many records
> have the same ID and vendor number.
>
> |ID | vendor_no | date |
> |2354 | 578 | "2005-12-23"|
> |2355 | 334 | "2005-12-24"|
> |2356 | 339 | "2005-12-26"|
> |2357 | 339 | "2005-12-26"|
> |2358 | 339 | "2005-12-26"|
> |2359 | 445 | "2005-12-26"|
> |2354 | 522 | "2005-12-27"|
> |2355 | 522 | "2005-12-27"|
>
> Would I use select count? Any help would be greatly appreciated.

I'd need a clearer spec to offer advise. What results would you want
from the example data? 2354 is there twice but with different vendor
numbers. And 522 is there twice with different IDs. One interpretation
of "[records with] the same ID and vendor number" is 0 because no record
has both the same as any other.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Andy Eastham

2006-02-25, 9:43 am

Richard,

If you mean with _both_ the same id _and_ vendor id, try this:

Select id, vendor_id, count(*) from tablename group by id, vendor_id;

If you just want separate counts for id and vendor_id, use:
Select id, count(*) from tablename group by id;
Select vendor_id, count(*) from tablename group by vendor_id;

Andy
> -----Original Message-----
> From: Richard Reina & #91;mailto:richard@r
ushlogistics.com]
> Sent: 23 February 2006 16:48
> To: mysql@lists.mysql.com
> Subject: query help?
>
> I am a novice when it come to queries such as this and was hoping someone
> could help me write a query that tells me how many records have the same
> ID and vendor number.
>
> |ID | vendor_no | date |
> |2354 | 578 | "2005-12-23"|
> |2355 | 334 | "2005-12-24"|
> |2356 | 339 | "2005-12-26"|
> |2357 | 339 | "2005-12-26"|
> |2358 | 339 | "2005-12-26"|
> |2359 | 445 | "2005-12-26"|
> |2354 | 522 | "2005-12-27"|
> |2355 | 522 | "2005-12-27"|
>
> Would I use select count? Any help would be greatly appreciated.
>
>
>
> A people that values its privileges above its principles soon loses both.
> -Dwight D. Eisenhower.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Richard Reina

2006-02-25, 9:43 am

--0-1658395879-1140716968=:61170
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I's so sorry. You are very correct. The sample data is bad. ID should be unique. Here it is corrected.

|ID | vendor_no | date |
|2354 | 578 | "2005-12-23"|
|2355 | 334 | "2005-12-24"|
|2356 | 339 | "2005-12-26"|
|2357 | 339 | "2005-12-26"|
|2358 | 339 | "2005-12-26"|
|2359 | 445 | "2005-12-26"|
|2360 | 522 | "2005-12-27"|
|2361 | 522 | "2005-12-27"|

cnelson@nycap.rr.com wrote: > I am a novice when it come to queries such as this and was hoping
> someone could help me write a query that tells me how many records
> have the same ID and vendor number.
>
> |ID | vendor_no | date |
> |2354 | 578 | "2005-12-23"|
> |2355 | 334 | "2005-12-24"|
> |2356 | 339 | "2005-12-26"|
> |2357 | 339 | "2005-12-26"|
> |2358 | 339 | "2005-12-26"|
> |2359 | 445 | "2005-12-26"|
> |2354 | 522 | "2005-12-27"|
> |2355 | 522 | "2005-12-27"|
>
> Would I use select count? Any help would be greatly appreciated.

I'd need a clearer spec to offer advise. What results would you want
from the example data? 2354 is there twice but with different vendor
numbers. And 522 is there twice with different IDs. One interpretation
of "[records with] the same ID and vendor number" is 0 because no record
has both the same as any other.

A people that values its privileges above its principles soon loses both.
-Dwight D. Eisenhower.
--0-1658395879-1140716968=:61170--

SGreen@unimin.com

2006-02-25, 9:43 am

--=_alternative 0062CAC88525711E_=
Content-Type: text/plain; charset="US-ASCII"

If you are looking just for duplicate (ID,vendort_no) combinations, this
will find them:

SELECT ID, vendor_no, count(1) as dupes
FROM table_name_here
GROUP BY ID, vendor_no
HAVING dupes >1;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Richard Reina < richard@rushlogistic
s.com> wrote on 02/23/2006 12:49:28 PM:

> I's so sorry. You are very correct. The sample data is bad. ID
> should be unique. Here it is corrected.
>
> |ID | vendor_no | date |
> |2354 | 578 | "2005-12-23"|
> |2355 | 334 | "2005-12-24"|
> |2356 | 339 | "2005-12-26"|
> |2357 | 339 | "2005-12-26"|
> |2358 | 339 | "2005-12-26"|
> |2359 | 445 | "2005-12-26"|
> |2360 | 522 | "2005-12-27"|
> |2361 | 522 | "2005-12-27"|
>
>
> cnelson@nycap.rr.com wrote: > I am a novice when it come to queries
> such as this and was hoping
>
> I'd need a clearer spec to offer advise. What results would you want
> from the example data? 2354 is there twice but with different vendor
> numbers. And 522 is there twice with different IDs. One interpretation
> of "[records with] the same ID and vendor number" is 0 because no record
> has both the same as any other.
>
>
>
>
>
> A people that values its privileges above its principles soon loses
both.
> -Dwight D. Eisenhower.
--=_alternative 0062CAC88525711E_=--

Richard Reina

2006-02-25, 9:43 am

--0-1263259350-1140718362=:83371
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Actually I am looking for duplicates (vedor_no, date), but I think I can hopefully adapt the solution you have given me.

SGreen@unimin.com wrote:
If you are looking just for duplicate (ID,vendort_no) combinations, this will find them:

SELECT ID, vendor_no, count(1) as dupes
FROM table_name_here
GROUP BY ID, vendor_no
HAVING dupes >1;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Richard Reina < richard@rushlogistic
s.com> wrote on 02/23/2006 12:49:28 PM:

> I's so sorry. You are very correct. The sample data is bad. ID
> should be unique. Here it is corrected.
>
> |ID | vendor_no | date |
> |2354 | 578 | "2005-12-23"|
> |2355 | 334 | "2005-12-24"|
> |2356 | 339 | "2005-12-26"|
> |2357 | 339 | "2005-12-26"|
> |2358 | 339 | "2005-12-26"|
> |2359 | 445 | "2005-12-26"|
> |2360 | 522 | "2005-12-27"|
> |2361 | 522 | "2005-12-27"|
>
>
> cnelson@nycap.rr.com wrote: > I am a novice when it come to queries
> such as this and was hoping
>
> I'd need a clearer spec to offer advise. What results would you want
> from the example data? 2354 is there twice but with different vendor
> numbers. And 522 is there twice with different IDs. One interpretation
> of "[records with] the same ID and vendor number" is 0 because no record
> has both the same as any other.
>
>
>
>
>
> A people that values its privileges above its principles soon loses both.
> -Dwight D. Eisenhower.

A people that values its privileges above its principles soon loses both.
-Dwight D. Eisenhower.
--0-1263259350-1140718362=:83371--

Sponsored Links