Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    count (*) from (select ....)

    Code:
    SELECT  Count(*)  FROM (SELECT * FROM table)
    --> Every derived table must have its own alias

    How to do this ?

    EDIT:

    huh, cant tell why now works.
    Code:
    SELECT  Count(*)  FROM (SELECT * FROM table) as Z
    Last edited by BubikolRamios; 04-05-2009 at 07:54 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    As the message said, your DERIVED TABLE (which is what that
    SELECT * FROM table
    is) needed an alias. Yes as Z is indeed an alias.

    NOW...

    Explain to us why you thought you *needed* a derived table????

    Why didn't you just code
    Code:
    SELECT COUNT(*) FROM table
    ????

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    explanation:
    (SELECT * FROM table) is just a demo

    real sql isn't simple as this, in fact it return something like this, forinstance 1 count column which is already grouped . including bunch of joins, and one complicated tip from you Old pedant :-).... -->

    3
    1
    6
    1

    and what I need is count of resulting rows, i.e. 4
    Last edited by BubikolRamios; 04-05-2009 at 08:57 AM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Okay, makes sense. But anyway, I guess as you noted, all you needed was the AS clause.

    This is, by the by, a MySQL requirement. SQL Server doesn't require it, for example.

    Yet another of the quirky differences between different dialects of SQL.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •