CODEWITHSUNDEEP
sql
user4940368
user4940368

Reputation:

How can I combine two queries into one

Using this query:

select C.CustomerID, P.ProductName, count(*) as Ordered
from Customers as C

join Orders as O on C.CustomerID = O.CustomerID
join [Order Details] as OD on O.OrderID = OD.OrderID
join Products as P on OD.ProductID = P.ProductID

group by C.CustomerID, P.ProductName

I can select customer's bought product's name and Ordered

CustomerID|ProductName |Ordered
--------------------------------
ANTON     |Alice Mutton|1
BERGS     |Alice Mutton|1
BLONP     |Alice Mutton|1
BOLID     |Alice Mutton|1
BONAP     |Alice Mutton|1

And using this:

select P.ProductName, count(*) as Ordered
from Products as P
join [Order Details] as OD on P.ProductID = OD.ProductID

group by P.ProductName

I can select how many times each product has been ordered:

ProductName      |Ordered
-------------------------
Alice Mutton     |37
Aniseed Syrup    |12
Boston Crab Meat |41
Camembert Pierrot|51
Carnarvon Tigers |27

Now, I want to combine theese two queries. I want to see how much each product was ordered by single Customer and what is total amount of orders of this product. How can I do that in one query?

Upvotes: 2

Views: 59

Answers (3)

JenInCode
JenInCode

Reputation: 250

Basically, you can put the items for the total orders and the customer orders into temp tables and then join them, as shown below. I chose Name to join on, but that may need to be adjusted, it is hard to tell. I like these more than sub queries, I think they are typically faster, but you could do a sub query in the join as well. Similar concept. This probably wont give you the final result you're looking for, but hopefully it will give you a good start.

select P.ProductName, count(*) as Ordered
    into #totalOrdered
    from Products as P
    join [Order Details] as OD on P.ProductID = OD.ProductID
    group by P.ProductName

    select C.CustomerID, P.ProductName, count(*) as Ordered
    into #CustomerOrders    
    from Customers as C
    join Orders as O on C.CustomerID = O.CustomerID
    join [Order Details] as OD on O.OrderID = OD.OrderID
    join Products as P on OD.ProductID = P.ProductID
    group by C.CustomerID, P.ProductName

select * 
From #CustomerOrders c
join #totalOrdered o on c.ProductName = o.ProductName

Upvotes: 0

Daniel Marcus
Daniel Marcus

Reputation: 2686

Are you looking for something like this?:

select a.*, b.ordered from (
select C.CustomerID, P.ProductName, count(*) as Ordered
from Customers as C
join Orders as O on C.CustomerID = O.CustomerID
join [Order Details] as OD on O.OrderID = OD.OrderID
join Products as P on OD.ProductID = P.ProductID
group by C.CustomerID, P.ProductName)a
left join
(select P.ProductName, count(*) as Ordered
from Products as P
join [Order Details] as OD on P.ProductID = OD.ProductID
group by P.ProductName)b
on a.ProductName=b.ProductName

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

To this:

select C.CustomerID, P.ProductName, count(*) as Ordered
from Customers as C

join Orders as O on C.CustomerID = O.CustomerID
join [Order Details] as OD on O.OrderID = OD.OrderID
join Products as P on OD.ProductID = P.ProductID

group by C.CustomerID, P.ProductName

add something like this:

union

select sum(0) as CustomerID, P.ProductName, count(*) as Ordered
from Orders as O 
join [Order Details] as OD on O.OrderID = OD.OrderID
join Products as P on OD.ProductID = P.ProductID

group by P.ProductName

Upvotes: 0

Related Questions