[SOLVED] Ordering COUNT output data

[Buchead]

Hello,

Hopefully someone can point me where I'm going wrong....

I have 2 tables:

venues - containing 'vID' and 'name'
shows - containing 'sID' and 'venue'

vID and sID are unique numbers for each venue/show. venues 'name' is the name of the venue, and shows 'venue' is vID value.

So:

VENUES                                    SHOWS
vID  name                                sID  venue

1      Fleece                              1      2
2      Academy                            2      1
3      Croft                                3      2
                                              4      3
                                              5      1


What I want to do is pull out the names and counts of the top 5 venues in alphabetical order. I can extract the data so that it orders on the count, but the name isn't in order. From the above I can get it listing as:

Fleece      2
Academy  2
Croft        1

but Fleece and Academy should be the other way around.

I'm using the command:

[code]SELECT COUNT(v.name) AS n,v.name FROM venues AS v INNER JOIN shows AS s WHERE s.venue=v.vID GROUP BY v.name ORDER BY n DESC[/code]


What am I missing?

Thanks,

Clive.

[artacus]

...ORDER BY n DESC, v.name ASC

[Buchead]

Many thanks for that. Too simple I missed it!

But would it be possible to throw another table into the mix?  So still with the VENUES and SHOWS tables, but adding the table FESTS that contains fID and VENUE. In the same way, fID is a unique number and VENUE corresponds to a vID held in VENUES.

So somehow I need to combine SHOWS and FESTS into one. Thought I could use UNION but that just throws up errors.

Could the best way to proceed be to run one query to create the list and pass that into the 2nd query?  I thought that maybe running an inner query could do the trick. Alas not. Perhaps I'm simply trying anything and getting too complicated.


Thanks for any advice.

[artacus]

Well just using joins would be awkward because if you have 8 shows and 3 fests you'll end up with 24 rows returned.

Union is the way to go here. Just fix it so it doesn't return errors :)  Hint: make sure you have the same number of columns in each section, post the query and error msg if that isn't enough help.

[Buchead]

I've been trying with unions, but clearly am putting it in the wrong place within the query.

I've tried:

[code]SELECT COUNT(v.name) AS n,v.name FROM `venues` AS v INNER JOIN `shows` as s UNION SELECT COUNT(v.place) AS n,v.name FROM `venues` AS v INNER JOIN `fests` as s WHERE s.venue=v.vID GROUP BY v.name ORDER BY n DESC,name ASC[/code]

and this gives the error:

Error - 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause


And clutching at straws I tried:

[code]SELECT COUNT(v.name) AS n,v.name FROM `venues` AS v INNER JOIN `shows` as s UNION `fests` as s WHERE s.venue=v.vID GROUP BY v.name ORDER BY n DESC,name ASC[/code]

but clearly that doesn't work as there's no real query behind the union.


The columns in the two tables (shows and fests) match exactly in title. Suppose I could have simply combined all the data into one table but wanted to keep them separate for some reason.

Any hints as to the correct direction to head are most appreciated!

[artacus]

1) Use some new lines in your queries so they're readable.

2) I lost touch with your original question in my last answer, you can do it like so:
[code]
SELECT v.name, COUNT(DISTINCT(s.sID)) AS num_shows,
COUNT(DISTINCT(f.fID)) AS num_fests
FROM `venues` AS v
LEFT JOIN `shows` as s ON v.vID = s.venue
LEFT JOIN fests AS f ON v.vID = f.venue
GROUP BY v.id ORDER BY num_shows + num_fests DESC, v.name[/code]

[Buchead]

That almost works, except it gives the error:

Error - 1054: Unknown column 'num_shows' in 'order clause'

If I remove either num_show or num_fests from the ORDER BY section it works, except for separating out the venues for shows and fests into 2 parts.


Thanks for the help so far though.

[artacus]

//except for separating out the venues for shows and fests into 2 parts.

If you want the count  to include both shows and fests in a single column:
[code]
COUNT(DISTINCT(s.sID)) +
COUNT(DISTINCT(f.fID)) AS n[/code]

[Buchead]

That's perfect. Thanks very much.    :)