Query to fetch data from two different tables

Hi,

I have two tables orders, and order_details. Now I want to write a query which will fetch the sum of order amount group by year and the sum total product quantity for that year from its detail.

Here are the table structure,

Orders table:
- id (PK)
- order_date
- total_amount
- shipping_charges
- discount_amount
- gift_wrap_charges

Order Details
- id
- order_id (FK)
- quantity
- unit_cost

Formula for total is (total_amount + shipping_charges + gift_wrap_charges - discount_amount)

I am using MySQL database.



----------------------
Shahzad Fateh Ali

LVL 9
Shahzad Fateh AliWeb Solutions Architect & Technical Project Manager- VentureDive (Pvt) LtdAsked:
Who is Participating?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
a simple group by should do:
select year(order_Date), sum( total_amount + shipping_charges + gift_wrap_charges - discount_amount) amount
  from Orders
group by year(order_Date)

Open in new window

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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and the qty:
select year(order_Date), sum(quantity) sum_qty
  from Orders
  join Order_Details
    on Orders.id = Order_Details.order_id
group by year(order_Date)

Open in new window

0
Shahzad Fateh AliWeb Solutions Architect & Technical Project Manager- VentureDive (Pvt) LtdAuthor Commented:
Hi angellll,

Thnx for swift reply but unfortunately i want both in single query.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

SStoryCommented:
SELECT YearOrdered,Sum(Total),Count(*) as QTY
FROM (
            SELECT (o.total_amount + o.shipping_charges + o.gift_wrap_charges - o.discount_amount) as    
            Total,Year(o.order_date) as YearOrdered
            FROM orders o
            INNER JOIN order_details d  ON o.id=d.order_id)
GROUP BY YearOrdered

Something like the above should do it
0
Shahzad Fateh AliWeb Solutions Architect & Technical Project Manager- VentureDive (Pvt) LtdAuthor Commented:
Hi SStory,

Here Count(*) as QTY fetches the count of total records selected, but i want sum of total quantity of orders group by year.

Formula will be
sum (sum ( quantity) of each order )

I hope that scene is clear now.
0
SStoryCommented:
SELECT YearOrdered,Sum(Total),Sum(quantity) as QTY
FROM (
            SELECT (o.total_amount + o.shipping_charges + o.gift_wrap_charges - o.discount_amount) as    
            Total,Year(o.order_date) as YearOrdered,d.quantity
            FROM orders o
            INNER JOIN order_details d  ON o.id=d.order_id)
GROUP BY YearOrdered

OK....how about the above?
0
SharathData EngineerCommented:
Check this:

SELECT B.Year_order_date,B.Total,A.SumQuantity FROM
(SELECT Year(A.order_date) AS Year_order_date,(A.total_amount + A.shipping_charges + A.gift_wrap_charges - A.discount_amount) as Total
  FROM Order A
 GROUP BY Year(A.order_date)) B
JOIN
(SELECT Year_order_date,SUM(Sum_quantity) AS SumQuantity FROM(
SELECT OD.order_id,Year(O.order_date) AS Year_order_date,SUM(qunatity) AS Sum_quantity
  FROM Order_Details OD
  JOIN Order O
    ON O.id = OD.order_id
 GROUP BY Year(O.order_date),OD.order_id) AS A
 GROUP BY Year_order_date) C
ON B.Year_order_date = C.Year_order_date
 
0
Shahzad Fateh AliWeb Solutions Architect & Technical Project Manager- VentureDive (Pvt) LtdAuthor Commented:
Thnx to you all that for you support, but not any of them is producing the correct result. Quantity is correct but total_amount is in correct.
0
SharathData EngineerCommented:
select year(order_Date), sum( total_amount + shipping_charges + gift_wrap_charges - discount_amount) amount
  from Orders
group by year(order_Date)

Did you get the correct amount with above query?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do (to combine):
select o.year_value, o.amount, q.sum_qty
from ( 
select year(order_Date) year_value
    , sum( total_amount + shipping_charges + gift_wrap_charges - discount_amount) amount
  from Orders
group by year(order_Date)
) o
join (
  select year(order_Date) year_value
   , sum(quantity) sum_qty
    from Orders
  join Order_Details
    on Orders.id = Order_Details.order_id
group by year(order_Date)
) q
on i.year_value = o.year_value

Open in new window

0
Shahzad Fateh AliWeb Solutions Architect & Technical Project Manager- VentureDive (Pvt) LtdAuthor Commented:
Thnx for your support.
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
MySQL Server

From novice to tech pro — start learning today.