Most Frequent string by region

[vjp2.at]

How would i get this to work? It complains baout not including the
aggregate in the result..

SELECT tract, region, Left([Phone],7) as FonXcg
FROM db1
Group by region
Order by Count(FonXcg) desc limit 1
Pivot by tract;



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.facebook.com/vasjpan2
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]

 

[MGFoster]

How would i get this to work? It complains baout not including the
aggregate in the result..

SELECT tract, region, Left([Phone],7) as FonXcg
FROM db1
Group by region
Order by Count(FonXcg) desc limit 1
Pivot by tract;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you want, but maybe this:

TRANSFORM COUNT(*) As theValue
SELECT region, Left(Phone,7) As FonXcg
FROM db1
GROUP BY region, Left(Phone,7)
PIVOT tract;

Or, perhaps, this:

SELECT region, Left(Phone,7) As FonXcg, COUNT(*) As theCount
FROM db1 As T1
GROUP BY region, Left(Phone,7)
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*) FROM db1
WHERE region = T1.region
ORDER BY COUNT(*) DESC )


HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSnCULoechKqOuFEgEQJUrACg4NwWr++vyl9ijjaZiM8V3smRia0An05t
HYBQGHBE85PEHU/1JYtO1eUu
=udob
-----END PGP SIGNATURE-----

 

[John Spencer]

Perhaps you could describe what you are attempting to do with the query.

Your syntax is in error.

Perhaps you want the following?

SELECT tract, region, Left([Phone],7) as FonXcg
FROM db1
Group by Tract, region, Left([Phone],7)
Order by Count(Left([Phone],7)) desc

You had a Pivot clause in there which would suggest you were attempting to use
a crosstab query, but there was no Transform clause.

Also, what is Limit 1 supposed to accomplish. Were you trying to return just
the TOP 1 record

SELECT TOP 1 tract, ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Vasos Panagiotopoulos]

Microsoft Jet engine cannot find T1..??

 

[MGFoster]

Microsoft Jet engine cannot find T1..??

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hmmmm...... the HAVING clause sub-query doesn't recognize the table
alias T1. How about this (just a guess):

SELECT region, FonXcg, COUNT(*) As theCount
FROM db1 As T1
WHERE FonXcg = (SELECT TOP 1 Left(Phone,7) As FonXcg
FROM db1
WHERE region = T1.region
GROUP BY Left(Phone,7)
ORDER BY COUNT(*) DESC)
GROUP BY region, FonXcg
ORDER BY COUNT(*) DESC

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSnOxAYechKqOuFEgEQIbiACgxWToCXAgdUSwH8MKhanG4C5IdYwAniem
rYz4OgcX2+SIUGmQ4DHo7drh
=UzW9
-----END PGP SIGNATURE-----