union and count

Hi all,

I've 3 tables. The first is a table of customers, the second a table of
orders and the third a table of questions.

I've to provide a table with the customerid, the number of orders and the
number of questions.
For every order and every question I've a new record in those table. The
tables are quite huge, so I can't send the structure but basically in the 3
tables they are the customerid number to link for.

As I general idea I'd like something like:

select name from customer, count(ordersid) from orders, count (questionid)
from customer
left join orders on customer.customerid = order.customerid
left join questions on customer.customerid = questions.customerid

Is this possible ? how ?

"Bob Bedford" <bob@bedford.com> a écrit dans le message de
news:467a93d0$0$3789$5402220f@news.sunrise.ch...
>
> select name from customer, count(ordersid) from orders, count (questionid)
> from customer
> left join orders on customer.customerid = order.customerid
> left join questions on customer.customerid = questions.customerid
>
> Is this possible ? how ?

try to add "group by customerid"
(look at this recent thread "speedest way to handle a select
table1,count(table2) where table1.idtable1=table2.idtable1 ?")

"patrice" <patrice_labracherie_nospam@free.fr> a écrit dans le message de
news: 467aa3ca$0$19103$426a74cc@news.free.fr...
> "Bob Bedford" <bob@bedford.com> a écrit dans le message de
> news:467a93d0$0$3789$5402220f@news.sunrise.ch...
>>
>> select name from customer, count(ordersid) from orders, count
>> (questionid)
>> from customer
>> left join orders on customer.customerid = order.customerid
>> left join questions on customer.customerid = questions.customerid
>>
>> Is this possible ? how ?
>
> try to add "group by customerid"
> (look at this recent thread "speedest way to handle a select
> table1,count(table2) where table1.idtable1=table2.idtable1 ?")

it works when I've only one count(), but if I've 2 counts from 2 diffent
tables, it multiplies the result of the first count by the result of the
second

I've 2 tables from wich I must do a count and I can't get a correct result.

Bob

On Thu, 21 Jun 2007 17:05:15 +0200, "Bob Bedford" wrote:

>I've 3 tables. The first is a table of customers, the second a table of
>orders and the third a table of questions.
>
>I've to provide a table with the customerid, the number of orders and the
>number of questions.
>For every order and every question I've a new record in those table. The
>tables are quite huge, so I can't send the structure but basically in the 3
>tables they are the customerid number to link for.
>[...]

Using "virtual tables", something like:

Select c.`name`, o.orders, q.questions
From customer c
Join (
Select customerid, count(ordersid) As orders
From orders
Group By customerid
) o On c.customerid = o.customerid,
Join (
Select customerid, count(questionid) As questions
From questions
Group By customerid
) q On c.customerid = q.customerid
Order By c.`name`

NB: just typed it in, you will need to play with it / fix it yourself.
--
Ross McKay, Toronto, NSW Australia
"Let the laddie play wi the knife - he'll learn"
- The Wee Book of Calvin

> Using "virtual tables", something like:
>
> Select c.`name`, o.orders, q.questions
> From customer c
> Join (
> Select customerid, count(ordersid) As orders
> From orders
> Group By customerid
> ) o On c.customerid = o.customerid,
> Join (
> Select customerid, count(questionid) As questions
> From questions
> Group By customerid
> ) q On c.customerid = q.customerid
> Order By c.`name`
>
> NB: just typed it in, you will need to play with it / fix it yourself.
> --
> Ross McKay, Toronto, NSW Australia
> "Let the laddie play wi the knife - he'll learn"
> - The Wee Book of Calvin

Splendid Mate ;-) !!! works fine.

Thanks a lot !