Grouping by more than column

[lxndr]

I have a table which holds details of various books and currently have a query which is used to provide a dropdown list of authors:

 

SELECT count(*) as total, author as author FROM library WHERE author <> 'Unknown' GROUP BY author ORDER BY author ASC

 

That works fine and is later used to provide a list like:

 

John Smith  34

Mary Jones  28

etc

 

but the table also has a column called 'coauthor' and what I want to be able to do is add in the results for coauthor as well as author to the grouping.  Is there a way of doing that in the query, grouping by multiple columns but as if they were one and the same?

 

Thanks in advance for any help.

 

__

 

[smerny]

GROUP BY author, coauthor

[lxndr]

GROUP BY author, coauthor

 

I tried that and it doesn't provide what I need.  Here's an example which might make it  a bit clearer:

 

 

Rec 1

author: John Smith

co-author:  Mary Jones

 

Rec 2

author: Mary Jones

co-author:

 

rec 3

author:  John Smith

co-author:  Fred Bloggs

 

 

I'd like the results for the above to output as:

 

Mary Jones 2

John Smith 2

Fred Bloggs 1

 

[premiso]

Not sure if this is the best way to do it, but here is one way by using a subquery:

 

SELECT author, (SELECT COUNT(coauthor) FROM library au WHERE au.coauthor = at.author) + COUNT(author) AS NumCount FROM library at GROUP BY author;

[lxndr]

Not sure if this is the best way to do it, but here is one way by using a subquery:

 

SELECT author, (SELECT COUNT(coauthor) FROM library au WHERE au.coauthor = at.author) + COUNT(author) AS NumCount FROM library at GROUP BY author;

 

Many thanks for this.  I thought perhaps something like this might be needed but my SQL knowledge was too basic to come up with it.  Having said that, I've just tried it on my database and it doesn't seem to have  picked up the co-authors ...

 

__

 

[JustLikeIcarus]

Heres one way it could be done.

 

select author, count(*)  as total
FROM ( 
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
)
GROUP BY author

[lxndr]

Heres one way it could be done.

 

select author, count(*)  as total
FROM ( 
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
)
GROUP BY author

 

Thanks for the suggestion.  When I run this though I get an error as follows:

 

Every derived table must have its own alias

 

 

[JustLikeIcarus]

Whoops try this

select t1.author, count(t1.*)  as total
FROM (
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author

[lxndr]

Whoops try this

select t1.author, count(t1.*)  as total
FROM (
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author

 

Thanks for the updated query, still giving an error though I'm afraid:

 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)  as total

FROM (

select author FROM library WHERE author <> 'Unknown'

UNIO' at line 1

[JustLikeIcarus]

Man I just suck today.  Ill cross my fingers this time

select t1.author, count(t1.author)  as total
FROM (
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author

[lxndr]

Man I just suck today.  Ill cross my fingers this time

select t1.author, count(t1.author)  as total
FROM (
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author

 

 

LOL. I suck far more than you because I didn't even know where to start

 

OK, well this time the query runs and the co-authors seem to have been included in the totals!  However, when I run the query in phpMyAdmin something strange happens.  It reports the number of rows as 265 and says 'showing rows 0 to 29' but then it actually shows ALL 265 rows and does the same for each of the 9 pages of results .. it also starts off with a row where the author field is blank and the total number of records in the table is shown against it?

 

 

[JustLikeIcarus]

Ok sounds like your getting whats known as a Cartesian product in phpmyadmin  change it to this.  If it has an issue with count(1) change it to count(*)

select t1.authors, count(1)  as total
FROM (
select authors FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as authors FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.authors

[lxndr]

Ok sounds like your getting whats known as a Cartesian product in phpmyadmin  change it to this.  If it has an issue with count(1) change it to count(*)

select t1.authors, count(1)  as total
FROM (
select authors FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as authors FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.authors

 

This query gives the following error:

 

Unknown column 'authors' in 'field list'

 

I tried renaming 'authors' to 'author' and that then executes but gives me the same result as previously with the mixed up row count...

 

 

 

[JustLikeIcarus]

I'm making myself mad now. Bad day

select t1.authors, count(1)  as total

FROM (

select author as authors FROM library WHERE author <> 'Unknown'

UNION ALL

select coauthor as authors FROM library WHERE coauthor <> 'Unknown'

) t1

GROUP BY t1.authors

 

[lxndr]

I'm making myself mad now. Bad day

select t1.authors, count(1)  as total

FROM (

select author as authors FROM library WHERE author <> 'Unknown'

UNION ALL

select coauthor as authors FROM library WHERE coauthor <> 'Unknown'

) t1

GROUP BY t1.authors

 

 

 

That's OK, I really appreciate the help.  This runs OK and I got rid of the blank author row by changing:

 

select coauthor as authors FROM library WHERE coauthor <> 'Unknown'

to

select coauthor as authors FROM library WHERE coauthor <> ''

 

So, basically the only problem now is that phpMyAdmin is displaying all 255 results on a page when it thinks it's only showing 30.  I'm not sure how this would get handled if I was to try and output the result using

 

while ($row = mysql_fetch_array($result)) 

 

from within my php script.

 

[JustLikeIcarus]

Ok so im not sure why your getting so many results.

 

What do you get back when you just from the union. I changed the not equal operator and the coauthor to not null

This should be a list of both authors and coauthors

 

select author as authors FROM library WHERE author != 'Unknown'
UNION ALL
select coauthor as authors FROM library WHERE coauthor IS NOT NULL

 

 

[premiso]

Here is a correction to Mr. Icarus's query:

 

select t1.author, count(1)  as total
FROM (
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author

 

That should work.

[lxndr]

Here is a correction to Mr. Icarus's query:

 

select t1.author, count(1)  as total
FROM (
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author

 

That should work.

 

That executes but I still get all the rows shown (255) on each page of the phpMyAdmin output rather than just 0-29. then 30-59 etc?

 

 

[JustLikeIcarus]

Here is a correction to Mr. Icarus's query:

 

select t1.author, count(1)  as total
FROM (
select author FROM library WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM library WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author

 

That should work.

 

I sure hope so premiso because i keep glossing over little things making it #fail lol.  Its funny how much harder it is to build queries when your not sitting at a sql prompt.  Sigh... I need a drink

[premiso]

That executes but I still get all the rows shown (255) on each page of the phpMyAdmin output rather than just 0-29. then 30-59 etc?

 

That is a LIMIT issue, you need to add that to the query:

select t1.author, count(1)  as total
FROM (
select author FROM author WHERE author <> 'Unknown'
UNION ALL
select coauthor as author FROM author  WHERE coauthor <> 'Unknown'
) t1
GROUP BY t1.author LIMIT 0, 30

 

Try that and see if that is better.

[JustLikeIcarus]

Did you run just the union statement?  I see no reason that it would return any more than twice the number of records in the table.

[JustLikeIcarus]

OH WAIT! Are we talking about pagination and not a bad query? Im so lost

[lxndr]

Ok so im not sure why your getting so many results.

 

What do you get back when you just from the union. I changed the not equal operator and the coauthor to not null

This should be a list of both authors and coauthors

 

select author as authors FROM library WHERE author != 'Unknown'
UNION ALL
select coauthor as authors FROM library WHERE coauthor IS NOT NULL

 

 

 

Yes, that works OK and lists both the authors and co-authors individually but I changed

 

select coauthor as authors FROM library WHERE coauthor IS NOT NULL

 

to

 

select coauthor as authors FROM library WHERE coauthor != '' 

 

as the co-author field doesn't use NULL when empty.

 

 

[JustLikeIcarus]

Ok so when you run the full group by query is the result correct?

[lxndr]

OH WAIT! Are we talking about pagination and not a bad query? Im so lost

 

That's probably my fault for not explaining clearly enough.  When I run the query that we ended up with within phpMyAdmin it executes successfully and says:

 

Showing rows 0 - 29 (264 total, Query took 0.0246 sec)

 

but

 

it then doesn't display just 30 rows as I'd expect but ALL of them, 264 in this case.  If I then select the second page of results (there's obviously 9 in total) I then don't get rows 30 - 59 but all of them again and so on for all the 9 pages.  Not sure if that explains what's happening any clearer?