Counting things with SQL

I have a database with a table of basket names and a table of items in those
baskets.
I would like to find the size of the basket with the most items in.
I cannot figure out how to do this is T-SQL:-
nMax=0
for each basketName in basket
n=count(records in basketItem where
basket.basketName=basketItem.basketName)
if n>nMax then n=nMax
next
Is there a simple way of doing it, or would it be more efficient to retrieve
the size of each basket into an array in vb.net and figure out the largest
one there?
Andrew
Here is an example for the Northwind database, can be easely compared to
your database with the right key associated.
Select O.OrderID,Count(*) from Orders O
Inner join [Order Details] OD
On O.OrderID = OD.OrderID
Group by O.OrderIDa
Count All Orders Group by the OrderID
(Yeah newsgroupreaders, i know you can easely only count those OrderDetails,
but in this case the poster has the basketname in the 1 "Orders" Table of
the 1:n relationship)
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Andrew Morton" <akm@.in-press.co.uk.invalid> schrieb im Newsbeitrag
news:uOMv$VcQFHA.3356@.TK2MSFTNGP12.phx.gbl...
>I have a database with a table of basket names and a table of items in
>those baskets.
> I would like to find the size of the basket with the most items in.
> I cannot figure out how to do this is T-SQL:-
> nMax=0
> for each basketName in basket
> n=count(records in basketItem where
> basket.basketName=basketItem.basketName)
> if n>nMax then n=nMax
> next
> Is there a simple way of doing it, or would it be more efficient to
> retrieve the size of each basket into an array in vb.net and figure out
> the largest one there?
> Andrew
>
|||"Jens Smeyer" wrote
> Here is an example for the Northwind database, can be easely compared to
> your database with the right key associated.
> Select O.OrderID,Count(*) from Orders O
> Inner join [Order Details] OD
> On O.OrderID = OD.OrderID
> Group by O.OrderIDa
Thanks for that - is the next line meant to be included in the script
somehow?
Is it intended to modify the above query to return only the largest value?

> Count All Orders Group by the OrderID
[Also, is the Northwind database available to those of us who only have MSDE
to experiment with? So many Microsoft examples use it, but without it it can
be hard to follow the examples.]
Andrew
|||[vbcol=seagreen]
///
No this line was just for explaination.

> [Also, is the Northwind database available to those of us who only have
> MSDE to experiment with? So many Microsoft examples use it, but without it
> it can be hard to follow the examples.]
Of course, look here:
http://www.microsoft.com/downloads/d...displaylang=en
HTH, Jens Smeyer.
http://www.sqlserver2005.de
|||hi Andrew,
Andrew Morton wrote:[vbcol=seagreen]
> "Jens Smeyer" wrote
> Thanks for that - is the next line meant to be included in the script
> somehow?
> Is it intended to modify the above query to return only the largest
> value?
nope, this is a short description of what the query is performing..

> [Also, is the Northwind database available to those of us who only
> have MSDE to experiment with? So many Microsoft examples use it, but
> without it it can be hard to follow the examples.]
http://www.microsoft.com/downloads/d...DisplayLang=en
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>>> Count All Orders Group by the OrderID
> ///
> No this line was just for explaination.
Jens and Andrea,
That makes sense now - I was looking in the help at ALL and wondering how
that connected with the query, and "the" didn't get coloured when I typed it
into the stored procedure editor in VS.NET so I knew I was going wrong
somewhere!
And thank you for the link to the sample databases download. I couldn't find
it by searching just because of the sheer number of items found.
Best regards,
Andrew