PDA

View Full Version : count (*) from (select ....)



BubikolRamios
04-05-2009, 07:52 AM
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.


SELECT Count(*) FROM (SELECT * FROM table) as Z

Old Pedant
04-05-2009, 08:10 AM
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


SELECT COUNT(*) FROM table

????

BubikolRamios
04-05-2009, 08:52 AM
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

Old Pedant
04-05-2009, 09:07 PM
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.