mysql select * with count

Question

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

Answer 1

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.