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.
LVL 4
jamesbcox1980Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SuperdaveCommented:
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".
0
LowfatspreadCommented:
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

0
LowfatspreadCommented:
Sorry Got the date range the wrong way around...
and forgot the order by.....


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,-31,getdate()) and dateadd(d,-1,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,-31,getdate()) and dateadd(d,-1,getdate())
        group by od.productcode  
     ) as B
  on A.Cartproductcode=B.productcode
Order by 2 desc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jamesbcox1980Author Commented:
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.
0
LowfatspreadCommented:
@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 ....



0
jamesbcox1980Author Commented:
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.
0
LowfatspreadCommented:
yes thats what i posted.....

Aliased as A in my proposed query....  34875757
0
jamesbcox1980Author Commented:
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!
0
jamesbcox1980Author Commented:
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.
0
jamesbcox1980Author Commented:
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.
0
LowfatspreadCommented:
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?
0
LowfatspreadCommented:
its probably the date range used then....

what is you other report using?

0
jamesbcox1980Author Commented:
Ok, I did some digging, and it was an issue with the report.  We're all good.  Thanks!
0
jamesbcox1980Author Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.