Solved

Select count from table, then get count from another table

Posted on 2011-02-11
14
880 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:jamesbcox1980
  • 7
  • 6
14 Comments
 
LVL 13

Expert Comment

by:Superdave
ID: 34875729
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34875730
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34875757
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
 
LVL 4

Author Comment

by:jamesbcox1980
ID: 34875775
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34875794
@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
 
LVL 4

Author Comment

by:jamesbcox1980
ID: 34876009
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34876109
yes thats what i posted.....

Aliased as A in my proposed query....  34875757
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 4

Author Comment

by:jamesbcox1980
ID: 34876850
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
 
LVL 4

Author Comment

by:jamesbcox1980
ID: 34888058
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
 
LVL 4

Author Comment

by:jamesbcox1980
ID: 34888090
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34888175
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34888192
its probably the date range used then....

what is you other report using?

0
 
LVL 4

Author Comment

by:jamesbcox1980
ID: 34888198
Ok, I did some digging, and it was an issue with the report.  We're all good.  Thanks!
0
 
LVL 4

Author Closing Comment

by:jamesbcox1980
ID: 34888210
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now