locked
Count Products Ordered RRS feed

  • Question

  • User-7389755 posted

     I have a table which displays all the items that a shopper has added to a shopping basket.

     

    Rather than show multiple selecttions of the same item how can I show the count of the number of the same items?

     

    My SQL select statement is:

     

    SELECT Orders.SessionID, Orders.OrderID, Orders.CustomerID, Orders.ProductID, Orders.ProductQty, Orders.ItemTotal, Orders.[Date], Orders.Status, Products.ProductName, Products.Price FROM (Orders INNER JOIN Products ON Orders.ProductID = Products.ProductID) WHERE (Orders.SessionID = ?)

     

    It is this part ( Orders.ProductQty ) which I want a count value for.

     

    Any help would be great.

    Tuesday, March 24, 2009 12:19 PM

Answers

  • User1096912014 posted

    I haven't tried it myself so I'm not sure it would work in Access

     

    select SessionID, OrderID, CustomerID, ProductID, TotalQty, ItemTotal, Date, Status, ProductName, Price from (SELECT Orders.SessionID, Orders.OrderID, Orders.CustomerID, Orders.ProductID, Orders.ProductQty, Orders.ItemTotal, Orders.[Date], Orders.Status, Products.ProductName, Products.Price FROM (Orders INNER JOIN Products ON Orders.ProductID = Products.ProductID) WHERE (Orders.SessionID = ?) ) Orders INNER JOIN (select sum(ProductQty) as TotalQty, CustomerID from Orders where (Orders.SessionID = ?) group by CustomerID) TotalPerCustomer on Orders.CustomerID = TotalPerCustomer.CustomerID

    This is from the top of my head (using derived tables idea), so it may not work exactly as is in Access (I know it would - may be after some tweaking - work in SQL Server))

    Hopefully you would be able to get it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 24, 2009 10:49 PM

All replies

  • User1096912014 posted

    SQL Server 2005 and up 

     SELECT Orders.SessionID, Orders.OrderID, Orders.CustomerID, Orders.ProductID, Orders.ProductQty, count(*) over (partition by Orders.CustomerID) as Orders_Count, Orders.ItemTotal, Orders.[Date], Orders.Status, Products.ProductName, Products.Price FROM (Orders INNER JOIN Products ON Orders.ProductID = Products.ProductID) WHERE (Orders.SessionID = ?)

    In prior versions of SQL Server we would need to use derived tables.

    Do we need to count the number per session or per Customer - adjust partition accordingly.

     

    Tuesday, March 24, 2009 12:44 PM
  • User-7389755 posted

     Unfortunately I get the following error:

     

    Error in SELECT clause: expression near '.'.
    Missing FROM clause.
    Error in SELECT clause: expression near ','.
    Unable to parse query text.

     

    Any clues withthis error message?

    Tuesday, March 24, 2009 1:42 PM
  • User1096912014 posted

    Did you get this error when you try this query in SSMS? Always try there first.

    What is your SQL Server version and what was the exact query you tried?

    Tuesday, March 24, 2009 1:54 PM
  • User-7389755 posted

     I am using Visual Web Developer and MS Access for the Database.

     

    I think another way to address this is have a select command something like:

    SELECT count(*) FROM [Orders] WHERE (([SessionID] = ?) AND ([ProductQty] = ?) AND ([ProductID] = ?))"

    If the count > 0 then don't add any more records.

     

    This is for a shop cart - I will let the shopper change the item qty on the grid view Edit/Update.

     

    I not sure how to address the condition for the check though.

     

     

    Tuesday, March 24, 2009 2:06 PM
  • User1096912014 posted

    You should have said it or I should have guessed it :) The query I wrote was for SQL Server. For Access we would need to use derived tables (I think Access supports it), but I'm very busy now, I'll write it at night from home.

    Tuesday, March 24, 2009 4:02 PM
  • User-7389755 posted

     Thanks very much for your help.

     

    Tuesday, March 24, 2009 4:23 PM
  • User1096912014 posted

    I haven't tried it myself so I'm not sure it would work in Access

     

    select SessionID, OrderID, CustomerID, ProductID, TotalQty, ItemTotal, Date, Status, ProductName, Price from (SELECT Orders.SessionID, Orders.OrderID, Orders.CustomerID, Orders.ProductID, Orders.ProductQty, Orders.ItemTotal, Orders.[Date], Orders.Status, Products.ProductName, Products.Price FROM (Orders INNER JOIN Products ON Orders.ProductID = Products.ProductID) WHERE (Orders.SessionID = ?) ) Orders INNER JOIN (select sum(ProductQty) as TotalQty, CustomerID from Orders where (Orders.SessionID = ?) group by CustomerID) TotalPerCustomer on Orders.CustomerID = TotalPerCustomer.CustomerID

    This is from the top of my head (using derived tables idea), so it may not work exactly as is in Access (I know it would - may be after some tweaking - work in SQL Server))

    Hopefully you would be able to get it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 24, 2009 10:49 PM