0

so here is my query

SELECT 
bia_clients.name "Builder Name", 
bia_panels.value "Name on Panel", 
bia_sizes.size "Size", 
bia_panels.sign_id "Sign Locations", 
count(bia_panels.sign_id) AS "Number of Panels", 
revenue_sharing AS "Amount Per Panel", 
count(bia_panels.sign_id) * revenue_sharing "Return to City"

FROM bia_panels
JOIN bia_projects ON bia_panels.project_id = bia_projects.id
JOIN bia_clients ON bia_clients.id = bia_projects.client_id
JOIN bia_sizes on bia_sizes.id = bia_panels.size_id
JOIN bia_prices ON bia_prices.size_id=bia_sizes.id
WHERE LEFT(bia_panels.sign_id, 3) = 042
group by bia_panels.value with rollup;

and this code works to grab my columns and totals as expected here

Builder Name    |   Name on Panel   |   Size    |   Sign Locations  |   Number of Panels    |   Amount Per Panel    |   Return to City
______________________________________________________________________________________________________________________________________
HAMPTON INN     |   Hampton Inn     |   Single  |   042182          |           2           |           19.00       |       38.00
BIA             |   Model Homes     |   Single  |   042301N         |           1           |           19.00       |       19.00
Richmond        |   American        |   Single  |   042092          |           3           |           19.00       |       57.00
Standard Pacific|   Standard Pacific|   Single  |   042102          |           7           |           19.00       |       133.00
Standard Pacific|   NULL            |   Single  |   042102          |           13          |           19.00       |       247.00

what i am having trouble with is getting the Sign Locations field i would like it to list each of the items that build the total for that column for example i would lilke my column to look like this

... |   Sign Locations          |   Number of Panels    |   ...
..._________________________________________________...
    |   042182, 042117          |   2                   |
    |   042301N                 |   1                   |
    |   042092, 042097, 042005  |   3
    ...

And as far as the formatting i can use some COALESCE statements to clean up the null lines i am not worried about that

but my strugle is how to make the Sign Locations field list all of the locations

i have tried to use a nested select statement (SELECT id from table where) but it still will only return the first item

any ideas?

happymacarts
  • 2,547
  • 1
  • 25
  • 33
  • Unrelated to your question but I hope bia_panels.value is enough to uniquely identify the columns you're selecting outside the aggregate! See http://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by and http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_only_full_group_by – aamit915 Nov 22 '13 at 00:55

1 Answers1

0

Try using GROUP_CONCAT()

From the docs:

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

In your case:

...
GROUP_CONCAT(bia_panels.sign_id) AS "Sign Locations", 
...
Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • Brilliant. The hardest part sometimes is trying to find out how to ask the question. Thank you very mush Answer accepted(well in 3 minutes I will) – happymacarts Nov 22 '13 at 00:38