Nested select

Can you tell me whats wrong with this query?
select count(*) as CountedOrders
from
(
select distinct [order]
from [OrdersTable]
where
[Customer]='100000' and
[Order Date] between '01/01/2005' and '31/12/2005'
)
It runs perfect in MS Access but not in MS SQL-Server.
I need to create a stored procedure that returns the number of orders from a
specific customer on a specific period.
Thanks in advance for your help.
"John" <John@.discussions.microsoft.com> wrote in message
news:E00259AC-FF4E-44D6-8730-5800CFDAC22A@.microsoft.com...
> Can you tell me whats wrong with this query?
> select count(*) as CountedOrders
> from
> (
> select distinct [order]
> from [OrdersTable]
> where
> [Customer]='100000' and
> [Order Date] between '01/01/2005' and '31/12/2005'
> )
> It runs perfect in MS Access but not in MS SQL-Server.
> I need to create a stored procedure that returns the number of orders from
> a
> specific customer on a specific period.
> Thanks in advance for your help.
SQL requires an alias for the derived table. Also ORDER has to be delimited
because it's a keyword (and therefore not a good choice for a column name).
It's also good practice to use a locale-independent date format like I have
done below.
SELECT COUNT(*) AS countedorders
FROM
(SELECT DISTINCT [order]
FROM OrdersTable
WHERE customer='100000'
AND [order date] BETWEEN '20050101' AND '20051231'
) AS T ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
(snip)
>SELECT COUNT(*) AS countedorders
> FROM
> (SELECT DISTINCT [order]
> FROM OrdersTable
> WHERE customer='100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> ) AS T ;
Hi David (& John),
Or even shorter:
SELECT COUNT(DISTINCT [order])
FROM OrdersTable
WHERE customer = '100000'
AND [order date] BETWEEN '20050101' AND '20051231'
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
|||Ο χρ?στη? "Hugo Kornelis" Xγγραψε:

> On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
> (snip)
> Hi David (& John),
> Or even shorter:
> SELECT COUNT(DISTINCT [order])
> FROM OrdersTable
> WHERE customer = '100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
>
Thanks you both David & Hugo!
Much appreciated!
John
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:316912561c41ogeut35basn6lq4h00n58j@.4ax.com...
> On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
> (snip)
> Hi David (& John),
> Or even shorter:
> SELECT COUNT(DISTINCT [order])
> FROM OrdersTable
> WHERE customer = '100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
I wonder if Order has any nulls?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Mon, 13 Mar 2006 21:02:57 -0000, David Portas wrote:
(snip)
>I wonder if Order has any nulls?
Hi David,
Good catch - I hadn't though of that.
(But in a table called "OrdersTable", I would really *hope* that the
Order column is NOT NULL...)
Hugo Kornelis, SQL Server MVP
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:jjvb12lparlmthnnllpsvrotgleknpm3ra@.4ax.com...
> (But in a table called "OrdersTable", I would really *hope* that the
> Order column is NOT NULL...)
>
Hugo, I would hope so too. But if the table is truly called "OrdersTable"
then I would *expect* that anything is possible. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx