asked on

Select count from table, then get count from another table

SELECT TOP 25 CartProductCode As "Product Code"
           ,COUNT(CartProductCode) AS "Cart Qty"
           ,SUM(CartItemQuantity) As "Total Qty"
FROM Cart 
WHERE DATEDIFF(day, LastModified, GETDATE())>= 1
    AND DATEDIFF(day, LastModified, GETDATE())<= 31
GROUP By CartProductCode
ORDER By COUNT(CartProductCode) DESC

Open in new window

I need to modify this SQL code. The code, as it is now, grabs the count of abandoned carts containing each product code for the last 30 days, then adds the total number of that product included in those carts.

This is the important information that I need, but it means nothing unless I can compare it to the number actually purchased. So, where my sample data looks like this:

Product Code        Cart Qty       Total Qty
SY7F29              438            580
SY5F29              414            664
SY13F3              297            298
SY13F9              210            571

Open in new window

I actually need it to look like this:

Product Code        Cart Qty       Aband Qty      Order Total        Purch Qty
SY7F29              438            580            4339               5149
SY5F29              414            664            9812               13757
SY13F3              297            298            1574               1763
SY13F9              210            571            6581               9442

Open in new window

The problem I'm running into is that the order total and purchase quantity are on a different table altogether, and to make it worse, the dates on those orders are in yet another table, so I have

Orders.OrderID
Orders.PurchaseDate
OrderDetails.OrderID
OrderDetails.ProductCode
OrderDetails.ItemQuantity

I figure I'll have to add a select statement inside of a FROM t INNER JOIN line. Any help would be appreciated.

Superdave

You could do sub-selects for that information:

SELECT TOP 25 CartProductCode As "Product Code"
,COUNT(CartProductCode) AS "Cart Qty"
,SUM(CartItemQuantity) As "Total Qty"
,(SELECT whatever ) As "Order Total"
,(SELECT whateven ) As "Purchase Qty"
FROM Cart
WHERE DATEDIFF(day, LastModified, GETDATE())>= 1
AND DATEDIFF(day, LastModified, GETDATE())<= 31
GROUP By CartProductCode
ORDER By COUNT(CartProductCode) DESC

Here "whatever" will have to sum whatever fields represent the order and include the DATEDIFF stuff on the other tables' date fields as well as comparing to "Product Code".

Lowfatspread

no a left outer join actually like this....

needs to be a left outer join since your top 25 abandoned carts may not have resulted in any
actual orders for those products (consider a new/advance launch scenario... with problems)

if you specify the last modified condition like this lastmodified ... then you have a better chance
that any index on last modified that you have will be used... otherwise the datediff function
will tend to preclude index usage....

similar with purchase date... (you didn't say what the appropriate date range for that should be...)

on a further datetime point .... normally you should probably drop the time portion of the date
at least from getdate() so that during the "day" you will get consistent results from any additional/
subsequent queries..

hth

Select A.CartProductcode  As "Product Code"
      ,A.[Cart Qty]
      ,A.[Total Qty] as [Aband Qty]
      ,coalesce(b.orders,0) as [Orders]
      ,coalesce(b.purchased,0} as [Purchased]
  from (
SELECT TOP 25 CartProductCode 
           ,COUNT(*) AS "Cart Qty"
           ,SUM(CartItemQuantity) As "Total Qty"
FROM Cart 
WHERE lastmodified between dateadd(d,-1,getdate()) and dateadd(d,-31,getdate())
GROUP By CartProductCode
ORDER By 2 DESC
     ) as A
Left Outer Join
     (select od.productcode  
             ,COUNT(distinct orderid) as [Orders]
             ,SUM(od.itemQuantity) as [Purchased]
         from OrderDetails as OD
        Inner Join Orders as O
           on Od.Orderid=o.orderid
          and O.purchaseDate between dateadd(d,-1,getdate()) and dateadd(d,-31,getdate())
        group by od.productcode   
     ) as B
  on A.Cartproductcode=B.productcode

Open in new window

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jamesbcox1980

ASKER

Actually, none of my carts will have resulted in order. As soon as the cart is purchased it is deleted from the abandoned carts table and an order I created pm the order tables. This is where I ran into a problem.

Lowfatspread

@james

not sure i see that as a problem?

you are still just wanting to supply abandoned vs ordered....

for your top 25 abandoned products ....

jamesbcox1980

ASKER

Correct. I'm simply trying pull the quantities for cart products, then pull the matching orders so that I can do comparison math on them later bin not sure we should join the tables, but rather pull from tables separately. I thought about putting my current statement into parentheses as it's own table, then use it to match the part numbers for the orders columns in a separate join

If that makes sense.

Lowfatspread

yes thats what i posted.....

Aliased as A in my proposed query.... 34875757

jamesbcox1980

ASKER

Ah thanks, I was on the train so I didn't have a chance to look at the script. I'll give it a try in the morning. Thanks a lot!

jamesbcox1980

ASKER

Ok, I had a chance to play with the query, and it ALMOST does exactly what I need it to do. The problem it's having is that the Abandoned Products columns are correctly reporting, but the purchased product quantities are not matching to the correct product code. I don't understand why, given the explicit Join condition of A.CartProductCode = B.ProductCode. I can't figure out why.

I had to add a new condition to the Orders section of the query, "AND O.OrderStatus <> 'Cancelled'", and I made some adjustments to the column names to they match the actual column names in the database, now that I know what they are. You'll notice PurchaseDate is now OrderDate and ItemQuantity is now just Quantity, for instance. Here's the new version of the Query that I'm using:

SELECT A.CartProductcode  AS [Product Code]
    ,A.[Abandoned Cart Qty] AS [Abandoned Cart Qty]
    ,A.[Total Qty] AS [Abandoned Qty]
    ,COALESCE(B.[Order Qty], 0) AS [Order Qty]
    ,COALESCE(B.[Purchased Qty], 0) AS [Purchased Qty]
FROM (
    SELECT TOP 25 CartProductCode
        ,COUNT(*) AS [Abandoned Cart Qty]
        ,SUM(CartItemQuantity) As [Total Qty]
    FROM Cart
    WHERE LastModified BETWEEN DATEADD(d, -31, GETDATE()) AND DATEADD(d, -1, GETDATE())
    GROUP BY CartProductCode
    ORDER BY 2 DESC
    ) AS A
LEFT OUTER JOIN (
    SELECT Od.ProductCode  
        ,COUNT(DISTINCT Od.OrderID) AS [Order Qty]
        ,SUM(Od.Quantity) AS [Purchased Qty]
    FROM OrderDetails AS OD
    INNER JOIN Orders AS O
        ON Od.OrderID = O.OrderID
        AND O.OrderDate BETWEEN DATEADD(d, -31, GETDATE()) AND DATEADD(d,-1, GETDATE())
        AND O.OrderStatus <> 'Cancelled'
    GROUP BY Od.ProductCode  
    ) AS B
    ON A.CartProductCode = B.ProductCode
ORDER BY 2 DESC

Open in new window

In the first file (RESULTS_OF_PRODUCTS_QUERY.csv), are the results of the above query. In the second file are the results of only the "B" query, which is the part of the query grabbing quantities of products actually purchased. RESULTS-OF-PRODUCTS-QUERY.csv RESULTS-OF-ORDERS-QUERY.csv

You'll notice right off the bat that the first product (CL-34S) is matching up with the quantities for CL-100 for purchased quantities.

jamesbcox1980

ASKER

Actually, sorry I'm wrong about that. They are matching correctly according to the second query's results. I was looking at an actual report from our database showing the purchased quantities and those are the numbers I was seeing that are different.

The reported quantities are definitely low, as the CL-34S should be somewhere around 1500 orders. I'm gonna look a little further to see where the problem is. I'll get back to you shortly.

Thanks.

Lowfatspread

You'll notice right off the bat that the first product (CL-34S) is matching up with the quantities for CL-100 for purchased quantities.

no i don't cl-34s matches perfectly?

A DO CL-100 AND CL-34SH

Are you certain there is a problem?

Lowfatspread

its probably the date range used then....

what is you other report using?

jamesbcox1980

ASKER

Ok, I did some digging, and it was an issue with the report. We're all good. Thanks!

jamesbcox1980

ASKER

Awesome, thanks a lot. The issue I had was with the Order status. The shopping cart software stores it as "Cancelled" but the report was capitalizing it for some reason, over reporting the numbers

Thanks!