Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT MAX(COUNT), A.id FROM
(
    SELECT supplier_id as id, COUNT(*) AS count
    FROM `library`.`lms_book_details`
    GROUP BY supplier_id
) GROUP BY A.id;

While executing the query, MySQL gives an error:

Every Derived table must have its own alias.

How do I add an alias to this?

share|improve this question
    
Add A after the closing parenthesis.It looks like a messed up query for a test. –  Mihai Feb 26 at 12:28
    
I guess your actual query is different because you don't need a derived table for the above. You are grouping by supplier_id in both the derived table and the external query. –  ypercube Feb 26 at 12:52
    
@ypercube Why do you think it doesn`t need an alias? –  Mihai Feb 26 at 16:26
    
@Mihai where did I say that? –  ypercube Feb 26 at 16:32
1  
@ypercube Apparently my parsing has bugs.. –  Mihai Feb 26 at 16:34

2 Answers 2

up vote 2 down vote accepted

Try like

SELECT MAX(A.count_supplier), A.id FROM
(
    SELECT supplier_id as id, COUNT(*) AS count_supplier
    FROM library.lms_book_details
    GROUP BY supplier_id
) A GROUP BY A.id;

Alias inner COUNT(*) as something different than COUNT as it is good practice to not use it as alias , As i have used count_supplier.

share|improve this answer
    
COUNT is not reserved but it's good practice to not use it as alias. –  ypercube Feb 26 at 12:57
    
@ypercube : Thanks for information ! –  Abdul Manaf Feb 26 at 13:12
SELECT MAX(COUNT), A.id FROM
(
                        SELECT supplier_id as id, COUNT(*) AS count
                        FROM library.lms_book_details
                        GROUP BY supplier_id
) as A GROUP BY A.id;
share|improve this answer
    
Why as alias Where is table A. This query will throw syntax error –  Abdul Manaf Feb 26 at 12:48
    
@AbdulManaf, yes sir i have give just example name :) –  Sagar Panchal Feb 26 at 12:49
    
There should also be space between FROM and library.lms_book_details, Please check the query syntax before posting answer –  Abdul Manaf Feb 26 at 12:51
2  
@Colin'tHart It's unnecessary for columns as well. Whether it is confusing or not, depends on the reader, right? –  ypercube Feb 26 at 16:31
1  
@ypercube Hmm, there goes my argument. I was under the impression that as was required for columns... –  Colin 't Hart Feb 26 at 17:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.