Complicated SQL Query

Question

My database looks like this :

• Customer (c_id, c_name)
• Products (p_id, p_name, p_cost)
• Buys (p_id references Products, c_id references Customer)

I want to query the database for the following:

• Customers with Maximum number of products: if 2 or more customers with same number of products are there, total price of all the products is considered

I have given it a try, but it shows an error saying : "Incorrect syntax near )".

I am trying to debug it to no gain.

Can anyone debug this Query or even suggest a more optimal Query?

select a1.c_id, a1.c_name 
from
    (select c.c_id, c.c_name
     from Customer c
     where c.c_id in
         (select b.c_id
          from Buys b
          group by b.c_id
          having COUNT(b.p_id) >= all
          (
              select COUNT(b.p_id)
              from Buys b
              group by b.c_id
          )
    )
) 
as a1 
join 
   (select b.c_id, SUM(p.p_cost) as 'SumCost'
    from Buys b 
    join Products p on b.p_id = p.p_id
    group by b.c_id
   ) as a2 on a1.c_id = a2.c_id
where 
    a2.SumCost = (select MAX(SumCost)
                  from 
                      (select b.c_id, SUM(p.p_cost) as 'SumCost' 
                       from Buys b 
                       join Products p on b.p_id = p.p_id
                       group by b.c_id
                      )
                 )

Update :

The above Query is debugged successfully by mohdowais. I now think this Query is not efficient. Can you all please suggest a more efficient one? I have not used any indexes.

Answer 1

You need to add a derived table specifier for the last select:

select a1.c_id,a1.c_name 
    from
    (
    select c.c_id,c.c_name
    from Customer c
    where c.c_id in
    (
        select b.c_id
        from Buys b
        group by b.c_id
        having COUNT(b.p_id)>=all
        (
            select COUNT(b.p_id)
            from Buys b
            group by b.c_id
        )
    )
) 
as a1 join   (
                    select b.c_id,SUM(p.p_cost) as 'SumCost'
                    from Buys b join Products p on (b.p_id=p.p_id)
                    group by b.c_id
                 )  
                 as a2  on (a1.c_id=a2.c_id) 

                 where a2.SumCost=
                 (
                    select MAX(SumCost)
                    from 
                    (
                        select b.c_id,SUM(p.p_cost) as 'SumCost' 
                        from Buys b join Products p on (b.p_id=p.p_id)
                        group by b.c_id
                    ) maxTmp    -- <-----------------
                 )

[I can't comment as to the correctness or efficiency of your query though, without the table schema, indexes, and example data]