p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Databases (http://p2p.wrox.com/forumdisplay.php?f=62)
-   -   Get Counts (http://p2p.wrox.com/showthread.php?t=349)

harpua June 9th, 2003 10:05 PM

Get Counts
 
Hello
Can somebody tell me a way to get this to work. I have one table with my Store Name and ID's and I have another table for Orders and OrderStatis. This will either be Pending or Filled, they are joined together by the StoreID. What I need is for each store to know the Count for both Pending orders and Filled orders. The database is Access.

I have tried using a Union Count statement but get errors, I'm thinking I may need to use something else but cannot figure out what.

strSQL = "SELECT StoreID, StoreName FROM Stores " &_
            "UNION SELECT COUNT DISTINCT(OrderPending) AS Pending FROM Orders WHERE StoreNo = OrdersStoreNo AND Statis = 'Pending' " &_
            "UNION SELECT COUNT DISTINCT(OrderPending) AS Filled FROM Orders WHERE StoreNo = OrdersStoreNo AND Statis = 'Filled' ;"

Any help or guidance is greatly appreciated.
Mike

Blaise June 9th, 2003 10:28 PM

If you are having troubles with your joins, why not try building two seperate recordsets...

Code:

strSQLP = "SELECT DISTINCT Count(*) AS PENDING " _
        & "FROM Orders " _
        & "WHERE StoreNo = OrdersStoreNo " _
        & "AND Statis = 'Pending'"

strSQLF = "SELECT DISTINCT Count(*) AS FILLED" _
        & "FROM Orders " _
        & "WHERE StoreNo = OrdersStoreNo " _
        & "AND Statis = 'Filled'"

Another way under ASP would be like this...

Code:

strSQL = "SELECT DISTINCT * FROM " _
       & "WHERE StoreNo = OrdersStoreNo"

SET Rs = Server.CreateObject("AODODB.RecordSet")
Rs.Open strSQL, objConn

Dim Pending, Filled
Pending = 0
Filled = 0

WHILE NOT Rs.EOF
  IF Rs("Statis") = "Pending" THEN Pending = Pending + 1
  IF Rs("Statis") = "Filled" THEN Filled = Filled + 1
  Rs.MoveNext
WEND

IF NOT Rs.BOF THEN Rs.MoveFirst

I hope this helps or maybe gives you a few more ideas as to what your problem might be.

Cheers!

harpua June 9th, 2003 10:52 PM

Thanks for the ideas my brain feels better. I thought of the 2 recordsets but was hoping there was a more efficient way of doing it with one statement, I'll use your second suggestion, again thanks the help.

KenSchaefer June 10th, 2003 12:45 AM

You can do this in a single SQL statement. Just use:

SELECT
   StoreID,
   StoreName,
   OrderStatus,
   COUNT(*)
FROM
   Stores
INNER JOIN
   Orders
ON
   Stores.StoreID = Orders.StoreID
GROUP BY
   StoreID, StoreName, OrderStatus
ORDER BY
   StoreID, OrderStatus DESC

Cheers
Ken

www.adOpenStatic.com


All times are GMT -4. The time now is 12:55 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.