Is this possible? (Row counting)

[milton747]

Hi Folks,
I would like to produce a rowset where one column
contains the count of rows within a collection.
Here are some sample tables to demonstrate the situation.



CUST_TABLE
custiD
C1
C2
C3

ORDER_TABLE
orderID custID
101 C1
122 C2
133 C3
139 C3

ITEMS-TABLE
orderID amount
101 $30
122 $93
122 $54
133 $16
133 $74
133 $12
139 $88

-------------------------------------
DESIRED_ROWSET
custID orderID amount row_count
C1 101 $30 1
C2 122 $93 2
C2 122 $54 2
C3 133 $16 3
C3 133 $74 3
C3 133 $12 3
C3 139 $88 1
-------------------------------------

The row_count column contains the number and rows
in the order (within the custId) eg: OrderID #133 has 3 rows.
I know it's not normalized, but the report layout portion
of the code is made HUGELY easier if I can determine
how many rows there are at the start of each order.


Any help welcome.
Thanks V.Much.
Milton.

 

[Remou]

Hi
See the last post in this thread701-1078906, it should help.

 

[cmmrfrds]

A correlated subquery to produce the row-count should work.

Select
custID,
orderID,
amount,
(Select count(*) From ITEMS-TABLE as IIT
Where IIT.customer_id = OIT.customer_id
and IIT.Order_id = OIT_Order_id
) as row_count
From ITEMS-TABLE as OIT

 

[milton747]

Hi cmmrfrds,

Thanks for reply. Here is what I tried based on your suggestion.


SELECT
customers.custID,
orders.orderID,
items.itemcode,
items.quantity,
(SELECT count(*)
From items as IIT
WHERE IIT.Order_id = OIT.Order_id
) as row_count
From items as OIT, customers, orders, items
WHERE customers.custID = orders.custID
AND orders.orderID = items.orderID
ORDER BY customers.custID, orders.orderID


GETS: Too Few parameters, Expected 2.


I added in the ORDER BY and WHERE at end per the original SQL.

Thanks.
Regs,
Milton.

 

[PHV]

And this ?
SELECT C.custID, rderID, I.itemcode, I.quantity,
(SELECT count(*) FROM items WHERE Order_id=O.Order_id) 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;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

Don't you need the items table only 1 time in the outer select. Once an alias is used, then it must be used on all references to the table.


SELECT
customers.custID,
orders.orderID,
OIT.itemcode,
OIT.quantity,
(SELECT count(*)
From items as IIT
WHERE IIT.Order_id = OIT.Order_id
) as row_count
From items as OIT, customers, orders
WHERE customers.custID = orders.custID
AND orders.orderID = OIT.orderID
ORDER BY customers.custID, orders.orderID

 

[milton747]

Hi PHV,

I just tried it. A slight col. name change and it works beautiful.
You are one hot SQL guru.

Thanks V much
Milton.

---

SELECT
C.custID,
rderID,
I.itemcode,
I.quantity,
(SELECT count(*)
FROM items
WHERE OrderID = O.OrderID) << alter to OrderID = etc...
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