...

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



BubikolRamios
04-05-2009, 08: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, 09: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, 09: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, 10: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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum