Doubling up Sub-Queries? 1

[milton747]

Hi folks,

The SQL_GOOD below works good. row_count delivers the number of row in each order.

I wanted to add extra commands to count the number of orders for each CustID. So I added a similar sub-quey, in various formats. But SQL complains. Any help, would be cool.

Thanks
Milton


* SQL_GOOD

SELECT
C.custID, rderID, C.creditrank,
C.repID, C.address, C.postalcode,
C.city, C.state, C.customName,
O.shipByDate, I.itemcode, I.quantity,
I.description, I.pricequote,
(SELECT count(*)
FROM items WHERE OrderID = O.OrderID) AS row_count
FROM (customers As C
INNER JOIN orders As O ON C.custID = O.custID)
INNER JOIN items As I ON rderID = I.orderID
ORDER BY 1, 2



* SQL_BAD
* Error Msg: error in FROM clause.


SELECT
C.custID, rderID, C.creditrank,
C.repID, C.address, C.postalcode,
C.city, C.state, C.customName,
O.shipByDate, O.custID, I.itemcode, I.quantity,
I.description, I.pricequote,
(SELECT count(*) FROM items WHERE OrderID = O.OrderID) AS row_count
FROM (customers As C
INNER JOIN orders As O ON C.custID = O.custID)
(SELECT count(*) FROM customers WHERE custID = O.CustID) AS o_count
FROM (orders As O
INNER JOIN customers As C ON C.custID = O.custID)
INNER JOIN items As I ON rderID = I.orderID
ORDER BY 1, 2


-- eom --

 

[rac2]

SELECT
   C.custID, O.orderID, C.creditrank,
   C.repID, C.address, C.postalcode,
   C.city, C.state, C.customName,
   O.shipByDate, O.custID, I.itemcode, I.quantity,
   I.description, I.pricequote,
   ( SELECT count(*)  FROM items 
     WHERE OrderID  =  O.OrderID
     ) AS row_count,
   ( SELECT count(*)  FROM customers 
     WHERE custID  =  O.CustID
     ) AS o_count

FROM customers As C
INNER JOIN orders As O ON C.custID = O.custID
INNER JOIN items As I ON O.orderID = I.orderID
ORDER BY 1, 2

 

[milton747]

Thanks big time rac.
Got it going good with your help.
A couple of slight tweaks.
See below.

Regs, Milt.


SELECT
C.custID, rderID, C.creditrank,
C.repID, C.address, C.postalcode,
C.city, C.state, C.customName,
O.shipByDate, O.custID, I.itemcode, I.quantity,
I.description, I.pricequote,
( SELECT count(*) FROM items
WHERE OrderID = O.OrderID
) AS row_count,
( SELECT count(*) FROM orders << orders...my mistake
WHERE custID = O.CustID
) AS o_count

FROM (customers As C << needed open parenthsis
INNER JOIN orders As O ON C.custID = O.custID) << needed close parenthesis
INNER JOIN items As I ON rderID = I.orderID
ORDER BY 1, 2


- eom -