Question: I have an existing mysql query that I need to add to and I'm not sure how to go about it.

Here is my current sql query.

```
SELECT tbl_brokerage_names.brokerage_id, tbl_brokerage_names.short_name,
    b.indication, b.max_indication
  FROM tbl_brokerage_names 
  LEFT JOIN (
    SELECT * FROM tbl_recommendation_brokerages
    WHERE recommendation_id = {$_GET['id']}
  ) b ON (tbl_brokerage_names.brokerage_id = b.brokerage_id) 
ORDER BY tbl_brokerage_names.short_name ASC

```

Here is the query that I need to work into the previous query.

```
 SELECT * , COUNT( * )
   FROM tbl_streetaccounts
   JOIN tbl_brokerage_names
 WHERE tbl_brokerage_names.brokerage_id = tbl_streetaccounts.brokerage_id

```

Basically I need to return a count, so I need to combine these two queries.

Answer: 
You should run these as two separate queries. 

The `COUNT(*)` query will return a single row, so there's no way to "combine" it with the first query while preserving the multi-row result of the first query.

Also, when you `SELECT *, COUNT(*)` you will get columns from some arbitrary row.

---

By the way, you have a glaring [SQL injection](http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies) vulnerability. Don't interpolate `$_GET` parameters directly in your SQL query. Instead, coerce it to an integer:

```
<?php
$id = (int) $_GET['id'];
$sql = "SELECT ... WHERE recommendation_id = {$id}";

```
Like @Bill said, you cannot get the count in every row without really weird syntax, but you can get an overall count using `GROUP BY ... WITH ROLLUP`.

e.g.:

```
<?php
$id = mysql_real_escape_string($_GET['id']); //works with anything, not just numbers
$query = "
  SELECT tbl_brokerage_names.brokerage_id
         , tbl_brokerage_names.short_name
         , b.indication
         , b.max_indication
         , count(*) as rowcount 
    FROM tbl_brokerage_names 
    LEFT JOIN (
      SELECT * FROM tbl_recommendation_brokerages
      WHERE recommendation_id = '$id'  //The single quotes are essential for safety!
    ) b ON (tbl_brokerage_names.brokerage_id = b.brokerage_id) 
  GROUP BY tbl_brokerage_names.brokerage_id WITH ROLLUP
  ORDER BY tbl_brokerage_names.short_name ASC
";

```

The `GROUP BY .. WITH ROLLUP` will add an extra line to the result with all NULL's for the non aggregated columns and a grand total count.

If you have any lines where `rowcount > 0` then you need to add extra clauses from table `b` to the `group by` clause to prevent MySQL from hiding arbitrary rows.  

Table `tbl_brokerage_names` is already fully defined because you are grouping by the primary key.