asp_databases thread: tricky SQL statement
Message #1 by "Ian Richardson" <ian@i...> on Thu, 25 Oct 2001 12:41:32
|
|
Hi,
could anyone help with the following SQL problem?
In the below example I need to search the whole record for distinct
records on the name field but I also need to search distinct on the last
field and count the number of times each distinct last field occurs
Record1
Name,Field2,Field3,Field4,Field5,Field6,Leeds
Record2
Name,Field2,Field3,Field4,Field5,Field6,Leeds
Record3
Name,Field2,Field3,Field4,Field5,Field6,London
Record4
Name,Field2,Field3,Field4,Field5,Field6,Athens
So the result of a search on the above example would be four records and
the list Leeds 2, London 1, Athens 1.
Is this possible? Any help would be gratefully aknowledged.
Cheers
Ian
Message #2 by David Cameron <dcameron@i...> on Fri, 26 Oct 2001 08:43:28 +1000
|
|
Assuming the commas mean separate fields and that the last field is called
City and that there is an identity in the table called ID:
SELECT City, COUNT(ID) AS CityCount
FROM MyTable
GROUP BY City.
If you are actually storing information as csv (comma separated values) then
I highly recommend that you read up on database normalisation, particularly
atomicity and the first normal form.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Ian Richardson [mailto:ian@i...]
Sent: Thursday, 25 October 2001 10:42 PM
To: ASP Databases
Subject: [asp_databases] tricky SQL statement
Hi,
could anyone help with the following SQL problem?
In the below example I need to search the whole record for distinct
records on the name field but I also need to search distinct on the last
field and count the number of times each distinct last field occurs
Record1
Name,Field2,Field3,Field4,Field5,Field6,Leeds
Record2
Name,Field2,Field3,Field4,Field5,Field6,Leeds
Record3
Name,Field2,Field3,Field4,Field5,Field6,London
Record4
Name,Field2,Field3,Field4,Field5,Field6,Athens
So the result of a search on the above example would be four records and
the list Leeds 2, London 1, Athens 1.
Is this possible? Any help would be gratefully aknowledged.
Cheers
Ian
Message #3 by "Ian Richardson" <ian@i...> on Fri, 26 Oct 2001 08:24:14 +0100
|
|
Hi David thank you for taking the trouble.
My actual SQL string is:
strSQL = "SELECT Distinct
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom Count(UID) As CityCount FROM infopack WHERE DateSiteLive >
" & strNow & " GROUP BY SitePaidFrom"
Which does not work as I get the error message
'Syntax error (missing operator) in query expression 'SitePaidFrom
Count(UID)'.
Do you have any ideas as to the best way to structure my query to get my
results. I do need to show all the data in the selected feilds plus I need
the name and number of occurances of the data in 'SitePaidFrom', which would
be the equivalent of the 'City' field in the example.
cheers
Ian
----- Original Message -----
From: "David Cameron" <dcameron@i...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, October 25, 2001 11:43 PM
Subject: [asp_databases] RE: tricky SQL statement
>
> Assuming the commas mean separate fields and that the last field is called
> City and that there is an identity in the table called ID:
>
> SELECT City, COUNT(ID) AS CityCount
> FROM MyTable
> GROUP BY City.
>
> If you are actually storing information as csv (comma separated values)
then
> I highly recommend that you read up on database normalisation,
particularly
> atomicity and the first normal form.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: Ian Richardson [mailto:ian@i...]
> Sent: Thursday, 25 October 2001 10:42 PM
> To: ASP Databases
> Subject: [asp_databases] tricky SQL statement
>
>
> Hi,
>
> could anyone help with the following SQL problem?
>
> In the below example I need to search the whole record for distinct
> records on the name field but I also need to search distinct on the last
> field and count the number of times each distinct last field occurs
>
> Record1
> Name,Field2,Field3,Field4,Field5,Field6,Leeds
> Record2
> Name,Field2,Field3,Field4,Field5,Field6,Leeds
> Record3
> Name,Field2,Field3,Field4,Field5,Field6,London
> Record4
> Name,Field2,Field3,Field4,Field5,Field6,Athens
>
> So the result of a search on the above example would be four records and
> the list Leeds 2, London 1, Athens 1.
>
> Is this possible? Any help would be gratefully aknowledged.
>
> Cheers
>
> Ian
Message #4 by "Drew, Ron" <RDrew@B...> on Fri, 26 Oct 2001 08:40:36 -0400
|
|
The Group BY should be on all the other fields without a function. You do
not Group By on a field you are counting. Try:
strSQL = "SELECT Distinct
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom, Count(UID) As CityCount FROM infopack WHERE DateSiteLive
>= " & strNow & " GROUP BY
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom"
-----Original Message-----
From: Ian Richardson [mailto:ian@i...]
Sent: Friday, October 26, 2001 3:24 AM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
Hi David thank you for taking the trouble.
My actual SQL string is:
strSQL = "SELECT Distinct
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom Count(UID) As CityCount FROM infopack WHERE DateSiteLive >
" & strNow & " GROUP BY SitePaidFrom"
Which does not work as I get the error message
'Syntax error (missing operator) in query expression 'SitePaidFrom
Count(UID)'.
Do you have any ideas as to the best way to structure my query to get my
results. I do need to show all the data in the selected feilds plus I need
the name and number of occurances of the data in 'SitePaidFrom', which would
be the equivalent of the 'City' field in the example.
cheers
Ian
----- Original Message -----
From: "David Cameron" <dcameron@i...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, October 25, 2001 11:43 PM
Subject: [asp_databases] RE: tricky SQL statement
>
> Assuming the commas mean separate fields and that the last field is
> called City and that there is an identity in the table called ID:
>
> SELECT City, COUNT(ID) AS CityCount
> FROM MyTable
> GROUP BY City.
>
> If you are actually storing information as csv (comma separated
> values)
then
> I highly recommend that you read up on database normalisation,
particularly
> atomicity and the first normal form.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: Ian Richardson [mailto:ian@i...]
> Sent: Thursday, 25 October 2001 10:42 PM
> To: ASP Databases
> Subject: [asp_databases] tricky SQL statement
>
>
> Hi,
>
> could anyone help with the following SQL problem?
>
> In the below example I need to search the whole record for distinct
> records on the name field but I also need to search distinct on the
> last field and count the number of times each distinct last field
> occurs
>
> Record1
> Name,Field2,Field3,Field4,Field5,Field6,Leeds
> Record2
> Name,Field2,Field3,Field4,Field5,Field6,Leeds
> Record3
> Name,Field2,Field3,Field4,Field5,Field6,London
> Record4
> Name,Field2,Field3,Field4,Field5,Field6,Athens
>
> So the result of a search on the above example would be four records
> and the list Leeds 2, London 1, Athens 1.
>
> Is this possible? Any help would be gratefully aknowledged.
>
> Cheers
>
> Ian
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #5 by Kyle Burns <kburns@c...> on Fri, 26 Oct 2001 16:19:55 -0500
|
|
Should also note the missing comma between "SitePaidFrom" and "Count(UID)".
The GROUP BY issue is one that you will have to face in addition to fixing
your current error, but the error message being received is a result of the
missing comma.
=================================
Kyle M. Burns, MCSD, MCT
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Friday, October 26, 2001 7:41 AM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
The Group BY should be on all the other fields without a function. You do
not Group By on a field you are counting. Try:
strSQL = "SELECT Distinct
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom, Count(UID) As CityCount FROM infopack WHERE DateSiteLive
>= " & strNow & " GROUP BY
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom"
-----Original Message-----
From: Ian Richardson [mailto:ian@i...]
Sent: Friday, October 26, 2001 3:24 AM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
Hi David thank you for taking the trouble.
My actual SQL string is:
strSQL = "SELECT Distinct
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom Count(UID) As CityCount FROM infopack WHERE DateSiteLive >
" & strNow & " GROUP BY SitePaidFrom"
Which does not work as I get the error message
'Syntax error (missing operator) in query expression 'SitePaidFrom
Count(UID)'.
Do you have any ideas as to the best way to structure my query to get my
results. I do need to show all the data in the selected feilds plus I need
the name and number of occurances of the data in 'SitePaidFrom', which would
be the equivalent of the 'City' field in the example.
cheers
Ian
----- Original Message -----
From: "David Cameron" <dcameron@i...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, October 25, 2001 11:43 PM
Subject: [asp_databases] RE: tricky SQL statement
>
> Assuming the commas mean separate fields and that the last field is
> called City and that there is an identity in the table called ID:
>
> SELECT City, COUNT(ID) AS CityCount
> FROM MyTable
> GROUP BY City.
>
> If you are actually storing information as csv (comma separated
> values)
then
> I highly recommend that you read up on database normalisation,
particularly
> atomicity and the first normal form.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: Ian Richardson [mailto:ian@i...]
> Sent: Thursday, 25 October 2001 10:42 PM
> To: ASP Databases
> Subject: [asp_databases] tricky SQL statement
>
>
> Hi,
>
> could anyone help with the following SQL problem?
>
> In the below example I need to search the whole record for distinct
> records on the name field but I also need to search distinct on the
> last field and count the number of times each distinct last field
> occurs
>
> Record1
> Name,Field2,Field3,Field4,Field5,Field6,Leeds
> Record2
> Name,Field2,Field3,Field4,Field5,Field6,Leeds
> Record3
> Name,Field2,Field3,Field4,Field5,Field6,London
> Record4
> Name,Field2,Field3,Field4,Field5,Field6,Athens
>
> So the result of a search on the above example would be four records
> and the list Leeds 2, London 1, Athens 1.
>
> Is this possible? Any help would be gratefully aknowledged.
>
> Cheers
>
> Ian
Message #6 by "Drew, Ron" <RDrew@B...> on Fri, 26 Oct 2001 17:44:05 -0400
|
|
Yes, I included the correction of the comma insert in my reply between
SitePaidFrom and Count(UID)
-----Original Message-----
From: Kyle Burns [mailto:kburns@c...]
Sent: Friday, October 26, 2001 5:20 PM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
Should also note the missing comma between "SitePaidFrom" and "Count(UID)".
The GROUP BY issue is one that you will have to face in addition to fixing
your current error, but the error message being received is a result of the
missing comma.
=================================
Kyle M. Burns, MCSD, MCT
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Friday, October 26, 2001 7:41 AM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
The Group BY should be on all the other fields without a function. You do
not Group By on a field you are counting. Try:
strSQL = "SELECT Distinct
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom, Count(UID) As CityCount FROM infopack WHERE DateSiteLive
>= " & strNow & " GROUP BY
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom"
-----Original Message-----
From: Ian Richardson [mailto:ian@i...]
Sent: Friday, October 26, 2001 3:24 AM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
Hi David thank you for taking the trouble.
My actual SQL string is:
strSQL = "SELECT Distinct
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
ive,SitePaidFrom Count(UID) As CityCount FROM infopack WHERE DateSiteLive >
" & strNow & " GROUP BY SitePaidFrom"
Which does not work as I get the error message
'Syntax error (missing operator) in query expression 'SitePaidFrom
Count(UID)'.
Do you have any ideas as to the best way to structure my query to get my
results. I do need to show all the data in the selected feilds plus I need
the name and number of occurances of the data in 'SitePaidFrom', which would
be the equivalent of the 'City' field in the example.
cheers
Ian
Message #7 by "Ian Richardson" <ian@i...> on Sat, 27 Oct 2001 07:51:08 +0100
|
|
Hi,
I've used drew's suggestion but it is not counting the the number of times
each 'distinct' SityepaidFrom field occurs. the result I need would be:
leeds 2
london 1
athens 1
what I am getting is
leeds 1
leeds 1
london 1
athens 1
I removed the Distinct fubction as I thought that this would remove the
duplicates I need, buit it still counts each record, not the number of times
leeds, london or athens is occurring.
----- Original Message -----
From: "Drew, Ron" <RDrew@B...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, October 26, 2001 10:44 PM
Subject: [asp_databases] RE: tricky SQL statement
> Yes, I included the correction of the comma insert in my reply between
> SitePaidFrom and Count(UID)
>
> -----Original Message-----
> From: Kyle Burns [mailto:kburns@c...]
> Sent: Friday, October 26, 2001 5:20 PM
> To: ASP Databases
> Subject: [asp_databases] RE: tricky SQL statement
>
>
> Should also note the missing comma between "SitePaidFrom" and
"Count(UID)".
> The GROUP BY issue is one that you will have to face in addition to fixing
> your current error, but the error message being received is a result of
the
> missing comma.
>
> =================================
> Kyle M. Burns, MCSD, MCT
> ECommerce Technology Manager
> Centra Credit Union
> kburns@c...
>
>
>
> -----Original Message-----
> From: Drew, Ron [mailto:RDrew@B...]
> Sent: Friday, October 26, 2001 7:41 AM
> To: ASP Databases
> Subject: [asp_databases] RE: tricky SQL statement
>
>
> The Group BY should be on all the other fields without a function. You do
> not Group By on a field you are counting. Try:
>
> strSQL = "SELECT Distinct
>
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
> ive,SitePaidFrom, Count(UID) As CityCount FROM infopack WHERE DateSiteLive
> >= " & strNow & " GROUP BY
>
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
> ive,SitePaidFrom"
>
> -----Original Message-----
> From: Ian Richardson [mailto:ian@i...]
> Sent: Friday, October 26, 2001 3:24 AM
> To: ASP Databases
> Subject: [asp_databases] RE: tricky SQL statement
>
>
> Hi David thank you for taking the trouble.
> My actual SQL string is:
> strSQL = "SELECT Distinct
>
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
> ive,SitePaidFrom Count(UID) As CityCount FROM infopack WHERE DateSiteLive
>
> " & strNow & " GROUP BY SitePaidFrom"
>
> Which does not work as I get the error message
> 'Syntax error (missing operator) in query expression 'SitePaidFrom
> Count(UID)'.
>
> Do you have any ideas as to the best way to structure my query to get my
> results. I do need to show all the data in the selected feilds plus I need
> the name and number of occurances of the data in 'SitePaidFrom', which
would
> be the equivalent of the 'City' field in the example.
>
> cheers
>
> Ian
Message #8 by David Cameron <dcameron@i...> on Mon, 29 Oct 2001 09:28:43 +1100
|
|
The DISTINCT keyword will have no effect on the COUNT. If you have records
that are returned twice and you want them removed from the aggregate you
might need to perform your query on another query.
eg
1st qry
SELECT DISTINCT <fields>
FROM <table>
SELECT <fields>, COUNT(ID) AS COUNT
FROM MyFirstQuery
I think that Access supports this.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Ian Richardson [mailto:ian@i...]
Sent: Saturday, 27 October 2001 4:51 PM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
Hi,
I've used drew's suggestion but it is not counting the the number of times
each 'distinct' SityepaidFrom field occurs. the result I need would be:
leeds 2
london 1
athens 1
what I am getting is
leeds 1
leeds 1
london 1
athens 1
I removed the Distinct fubction as I thought that this would remove the
duplicates I need, buit it still counts each record, not the number of times
leeds, london or athens is occurring.
Message #9 by "Drew, Ron" <RDrew@B...> on Mon, 29 Oct 2001 08:45:38 -0500
|
|
Try this:
If UID is a unique ID assigned, it will not work and you should just delete
it both in the select and group by..
If it does not show the SitePaidFrom, just add it to the select without a
function and add it to the group by..
SELECT UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,
DateSiteLive, Count(SitePaidFrom) As CityCount
FROM infopack
WHERE DateSiteLive = " & strNow & "
GROUP BY UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,
DateSiteLive
-----Original Message-----
From: Ian Richardson [mailto:ian@i...]
Sent: Saturday, October 27, 2001 2:51 AM
To: ASP Databases
Subject: [asp_databases] RE: tricky SQL statement
Hi,
I've used drew's suggestion but it is not counting the the number of times
each 'distinct' SityepaidFrom field occurs. the result I need would be:
leeds 2 london 1 athens 1 what I am getting is leeds 1 leeds 1 london 1
athens 1 I removed the Distinct fubction as I thought that this would remove
the duplicates I need, buit it still counts each record, not the number of
times leeds, london or athens is occurring.
----- Original Message -----
From: "Drew, Ron" <RDrew@B...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, October 26, 2001 10:44 PM
Subject: [asp_databases] RE: tricky SQL statement
> Yes, I included the correction of the comma insert in my reply between
> SitePaidFrom and Count(UID)
>
> -----Original Message-----
> From: Kyle Burns [mailto:kburns@c...]
> Sent: Friday, October 26, 2001 5:20 PM
> To: ASP Databases
> Subject: [asp_databases] RE: tricky SQL statement
>
>
> Should also note the missing comma between "SitePaidFrom" and
"Count(UID)".
> The GROUP BY issue is one that you will have to face in addition to
> fixing your current error, but the error message being received is a
> result of
the
> missing comma.
>
> =================================
> Kyle M. Burns, MCSD, MCT
> ECommerce Technology Manager
> Centra Credit Union
> kburns@c...
>
>
>
> -----Original Message-----
> From: Drew, Ron [mailto:RDrew@B...]
> Sent: Friday, October 26, 2001 7:41 AM
> To: ASP Databases
> Subject: [asp_databases] RE: tricky SQL statement
>
>
> The Group BY should be on all the other fields without a function. You
> do not Group By on a field you are counting. Try:
>
> strSQL = "SELECT Distinct
>
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
> ive,SitePaidFrom, Count(UID) As CityCount FROM infopack WHERE
> DateSiteLive
> >= " & strNow & " GROUP BY
>
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
> ive,SitePaidFrom"
>
> -----Original Message-----
> From: Ian Richardson [mailto:ian@i...]
> Sent: Friday, October 26, 2001 3:24 AM
> To: ASP Databases
> Subject: [asp_databases] RE: tricky SQL statement
>
>
> Hi David thank you for taking the trouble.
> My actual SQL string is:
> strSQL = "SELECT Distinct
>
UID,Name,Address,Country,PostCode,Email,PackageOfInterest,UserName,DateSiteL
> ive,SitePaidFrom Count(UID) As CityCount FROM infopack WHERE
>DateSiteLive = " & strNow & " GROUP BY SitePaidFrom"
>
> Which does not work as I get the error message
> 'Syntax error (missing operator) in query expression 'SitePaidFrom
> Count(UID)'.
>
> Do you have any ideas as to the best way to structure my query to get
> my results. I do need to show all the data in the selected feilds plus
> I need the name and number of occurances of the data in
> 'SitePaidFrom', which
would
> be the equivalent of the 'City' field in the example.
>
> cheers
>
> Ian
|
View Cart
