[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Logic Query

Posted on 2009-02-23
7
Medium Priority
?
301 Views
Last Modified: 2012-05-06
Hello

I have two tables an orderline table and an item tables, the orderline table has an orderID and a stockID as the unquie keys, while the item table has the stockID as the primary key with a field knows as productclass.

I want execute a query that will count the number of orders with their value, in which there was a product  A ordered alone, and another column that will count the number of orders in which product  A  exist with product  B  in particular, and another column that will count the number of orders with product  A and any other product (Product C, D, E, F....)
0
Comment
Question by:M_Boy76
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23709757
How about this ?
SELECT	 COUNT(ProdAOnly.OrderId) AS ProductAOnlyCount
	,COUNT(ProdAB.OrderId) AS ProductABCount
	,COUNT(ProdAOther.OrderId) AS ProductAOtherCount
FROM
	(SELECT OrderId, COUNT(DISTINCT StockId) FROM T_OrderLine WHERE TL.StockId LIKE 'Product A' 
	 GROUP BY OrderId HAVING COUNT(DISTINCT StockId) = 1
	) ProdAOnly
RIGHT JOIN
	(SELECT OrderId, COUNT(DISTINCT StockId) FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' OR TL.StockId LIKE 'Product B')
	 GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
	) ProdAB
	ON ProdAOnly.OrderId = ProdAB.OrderId
RIGHT JOIN
	(SELECT OrderId, COUNT(DISTINCT StockId) FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' AND TL.StockId NOT LIKE 'Product B')
	 GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
	) ProdAOther
	ON ProdAOther.OrderId = ProdAB.OrderId

Open in new window

0
 

Author Comment

by:M_Boy76
ID: 23709883
I got the following error:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'ProdAOnly'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'ProdAB'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'ProdAOther'.
0
 
LVL 25

Expert Comment

by:reb73
ID: 23709894
Sorry.. Try -
SELECT   COUNT(ProdAOnly.OrderId) AS ProductAOnlyCount
        ,COUNT(ProdAB.OrderId) AS ProductABCount
        ,COUNT(ProdAOther.OrderId) AS ProductAOtherCount
FROM
        (SELECT OrderId, COUNT(DISTINCT StockId) AS TempCount FROM T_OrderLine WHERE TL.StockId LIKE 'Product A' 
         GROUP BY OrderId HAVING COUNT(DISTINCT StockId) = 1
        ) ProdAOnly
RIGHT JOIN
        (SELECT OrderId, COUNT(DISTINCT StockId) AS TempCount FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' OR TL.StockId LIKE 'Product B')
         GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
        ) ProdAB
        ON ProdAOnly.OrderId = ProdAB.OrderId
RIGHT JOIN
        (SELECT OrderId, COUNT(DISTINCT StockId) AS TempCount FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' AND TL.StockId NOT LIKE 'Product B')
         GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
        ) ProdAOther
        ON ProdAOther.OrderId = ProdAB.OrderId

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:M_Boy76
ID: 23709971
Layout was perfect but for some strange reason it returned zero count in all three columns...
0
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23709992
Can you change the RIGHT JOIN to FULL OUTER JOIN and retry?

Also try executing the subqueries separately to see what they return..
0
 
LVL 6

Expert Comment

by:FVER
ID: 23710067
the query below only counts the orders.
You need to be more specific on the "value" you want to sum.
WITH Orders_View as (
  SELECT orderID,
         sum(case stockID 
                 when 'product A ID' then 1
                 else 0
            end) PA,
         sum(case stockID 
                 when 'product B ID' then 1
                 else 0
            end) PB,
         sum(case stockID 
                 when 'product A ID' then 0
                 when 'product B ID' then 0
                 else 1
            end) otherP
    from orderline
  group by orderID
)
SELECT sum(case when PA=1 and PB=0 and otherP=0 then 1 else 0 end) PA_only,
       sum(case when PA=1 and PB=1 /*and otherP=0*/ then 1 else 0 end) PA_PB,
       sum(case when PA=1 and PB=0 and otherP>=1 then 1 else 0 end) PA_other
  FROM Orders_View

Open in new window

0
 

Author Comment

by:M_Boy76
ID: 23712913
@ reb73: what is i want to sum the unit price for each of

COUNT(ProdAOnly.OrderId) AS ProductAOnlyCount
        ,COUNT(ProdAB.OrderId) AS ProductABCount
        ,COUNT(ProdAOther.OrderId) AS ProductAOtherCount
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 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