Learning SQL coding techniques
Posted on 2011-02-24
I am also looking for another coding example for this. I learn best by code examples. Any help would be greatly appreciated. I have tried several different ways but I would love to see how an experienced Pro would do this.
Given the following tables and sample data:
create table Company ( CompanyID int, CompanyName varchar(30) )
insert Company values (1, 'Acme Construction')
insert Company values (2, 'AAA Buildings')
create table Item ( ItemID int, ItemName varchar(10), UnitCost money )
insert Item values ( 1, 'Widget', 1.00) -- each unit $1
insert Item values ( 2, 'Sprocket', 2.00) -- each unit $2
create table ItemOrder ( ItemOrderID int, CompanyID int, ItemID int, OrderDate datetime, UnitsOrdered int )
-- Acme orders 3 widgets on 10/5
insert ItemOrder values ( 1, 1, 1, '10/5/05', 3 )
-- AAA Buildings orders 1 sprocket on 11/1
insert ItemOrder values ( 2, 2, 2, '11/1/05', 1 )
My manager says: ""I want to know, for every company and item combination, the following
information: total orders, total units ordered, total cost of units ordered, first order date,
and last order date.
The result set should be:
CompanyName, ItemName, TotalOrders, TotalUnitsOrdered, TotalOrderCost, FirstOrderDate, LastOrderDate
Write the SQL for this.