I have this query
SELECT *, COUNT(*) as count FROM PricePaid WHERE Postcode LIKE 'L23 0TP%' GROUP BY Postcode
I am getting this result :
L23 0PT House number 1 Bella Grove LIVERPOOL 2
But what I am looking for is a way to get this result :
L23 0PT House number 1 Bella Grove LIVERPOOL 2
L23 0PT House number 17 Bella Grove LIVERPOOL 2
You see I am displaying the results on a google map So initially I want to display a marker on the map showing each post code with the number of houses sold on the post code ( the count(*)part )
And then I need display a list of the propeties from that street in the same query to pass to an info window to then display all the properties on that post code.
Any one any idea if I can do this on one query, any pointers would be greatly appreciated thanks
You are misusing a nonstandard MySQL extension to GROUP BY. See this: http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html
You have two things going on in this query. One is to provide some details from your table. The other is to present an aggregate. You can't do both without using a subquery.
Here is what you need:
SELECT pp.*, cpp.count
FROM PricePaid AS pp
JOIN (
SELECT Postcode, COUNT(*) AS count
FROM PricePaid
GROUP BY PostCode
) AS cpp ON pp.Postcode = cpp.Postcode
WHERE pp.Postcode LIKE 'L23 0TP%'
Do you see the virtual summary (aggregate) table JOINed to your physical table? It does the count of properties by postcode.
SELECT Postcode, COUNT(*) AS count
FROM PricePaid
GROUP BY PostCode
The JOIN picks up the count for all the properties in the Postcode and includes it in the result set rows with the detail items.
The problem (I'm guessing without being able to see your schema) is that you are grouping by the postcode. Your desired results each have the same postal code, so they are shown in one row. If you want to have each row and get each row with a count of all of the items, then you should remove your group by statement.