Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 13 total |
select customers with more then one occurence |
Sat, Jul 28 2007 10:04 AM | Permanent Link |
"Harry de Boer" | LS
dbIsam 3.3. We copied from several companies their customer tables (same structure) to one table with an extra field that holds the company. How do I query this table so that the result is only customers that belong to more then one company? Postal code and SurName are the identifiers. Regards, Harry |
Sat, Jul 28 2007 10:54 AM | Permanent Link |
Steve Forbes Team Elevate | Hi Harry,
Something like .. SELECT Surname, Company FROM Customer WHERE COUNT(Surname) > 1 GROUP BY Company, Surname; -- Best regards Steve "Harry de Boer" <harry@staaf.nl> wrote in message news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com... > LS > > dbIsam 3.3. > > We copied from several companies their customer tables (same structure) to > one table with an extra field that holds the company. How do I query this > table so that the result is only customers that belong to more then one > company? Postal code and SurName are the identifiers. > > Regards, Harry > > |
Sat, Jul 28 2007 11:48 AM | Permanent Link |
"Harry de Boer" | Steve,
Alas: this is -for dbIsam 3.3- not a correct syntax. Regards, Harry "Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> schreef in bericht news:5A81AE9B-FC3B-4E1B-8941-19F19EF5209E@news.elevatesoft.com... > Hi Harry, > > Something like .. > > SELECT Surname, Company FROM Customer WHERE COUNT(Surname) > 1 GROUP BY > Company, Surname; > > -- > Best regards > > Steve > > "Harry de Boer" <harry@staaf.nl> wrote in message > news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com... > > LS > > > > dbIsam 3.3. > > > > We copied from several companies their customer tables (same structure) to > > one table with an extra field that holds the company. How do I query this > > table so that the result is only customers that belong to more then one > > company? Postal code and SurName are the identifiers. > > > > Regards, Harry > > > > > > |
Sat, Jul 28 2007 6:23 PM | Permanent Link |
"Trevor Keegan" | Hello Harry,
> Alas: this is -for dbIsam 3.3- not a correct syntax. It would probably be a good idea for you to then post your question into dbisam.sql to avoid confusion. Off the top of my head, I think that this should work: SELECT Surname, Company, COUNT(Surname) AS CustomeCount FROM Customer GROUP BY Company,Name HAVING CustomerCount > 1 Regards Trevor Keegan |
Sun, Jul 29 2007 6:10 AM | Permanent Link |
"Harry de Boer" | Trevor,
AFAICS this only works if the same company name for a customer occurs more then once. What I need is a list of customers that belongs to more then one company. Regards, Harry "Trevor Keegan" <tkeegan@ealink.com> schreef in bericht news:9C5ED5EF-DA73-4E70-9661-01E13DE46092@news.elevatesoft.com... > Hello Harry, > > > Alas: this is -for dbIsam 3.3- not a correct syntax. > It would probably be a good idea for you to then post your question into > dbisam.sql to avoid confusion. > > Off the top of my head, I think that this should work: > > SELECT Surname, Company, COUNT(Surname) AS CustomeCount FROM Customer GROUP > BY Company,Name HAVING CustomerCount > 1 > > Regards > Trevor Keegan > > |
Sun, Jul 29 2007 10:30 AM | Permanent Link |
"Trevor Keegan" | Hello Harry,
Then it should work if you change the query to: SELECT Surname, Company, COUNT(Company) AS CompanyCount FROM Customer GROUP BY Company,Surname HAVING CompanyCount > 1 Regards Trevor Keegan |
Mon, Jul 30 2007 4:38 AM | Permanent Link |
"Harry de Boer" | Trevor
I'm sorry but that seems to be the same. I tested it with a surname 'Groote' that occurs two times with different companies in the table. It is not in the resultlist of the query however. Records with more then one occurence that have the same surname/company are. So this seems to be not the answer (I must be honest that I too thought it would be the right query statement). Regards, Harry "Trevor Keegan" <tkeegan@ealink.com> schreef in bericht news:D8726E34-B8A1-4880-BDB0-FAD74C4BD9B5@news.elevatesoft.com... > Hello Harry, > > Then it should work if you change the query to: > > SELECT Surname, Company, COUNT(Company) AS CompanyCount FROM Customer GROUP > BY Company,Surname HAVING CompanyCount > 1 > > Regards > Trevor Keegan > > |
Mon, Jul 30 2007 4:47 AM | Permanent Link |
"John Hay" | Harry,
Assuming that both postcode and surname need to match and that you also want to select those with duplicates in the same company how about - SELECT Surname, Postcode, COUNT(Surname) AS CompanyCount FROM Customer GROUP BY Surname,Postcode HAVING CompanyCount > 1 John "Harry de Boer" <harry@staaf.nl> wrote in message news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com... > LS > > dbIsam 3.3. > > We copied from several companies their customer tables (same structure) to > one table with an extra field that holds the company. How do I query this > table so that the result is only customers that belong to more then one > company? Postal code and SurName are the identifiers. > > Regards, Harry > > |
Mon, Jul 30 2007 7:56 AM | Permanent Link |
"Harry de Boer" | John,
Both postcode and surname need to match, but the company can differ. What I need is a list of customers that belongs to more then one company. Regards, Harry "John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> schreef in bericht news:3D956517-E38C-45C2-BA85-A356B3355172@news.elevatesoft.com... > Harry, > > Assuming that both postcode and surname need to match and that you also want > to select those with duplicates in the same company > how about - > > SELECT Surname, Postcode, COUNT(Surname) AS CompanyCount FROM Customer GROUP > BY Surname,Postcode HAVING CompanyCount > 1 > > John > > > "Harry de Boer" <harry@staaf.nl> wrote in message > news:4C7517B7-7918-4D7C-8DC6-3D52B47FC419@news.elevatesoft.com... > > LS > > > > dbIsam 3.3. > > > > We copied from several companies their customer tables (same structure) to > > one table with an extra field that holds the company. How do I query this > > table so that the result is only customers that belong to more then one > > company? Postal code and SurName are the identifiers. > > > > Regards, Harry > > > > > > |
Mon, Jul 30 2007 8:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
<< dbIsam 3.3. >> Wrong newsgroup. << We copied from several companies their customer tables (same structure) to one table with an extra field that holds the company. How do I query this table so that the result is only customers that belong to more then one company? Postal code and SurName are the identifiers. >> What John Hay said should work, provided that the Company is unique for each row. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |