I am having troubles with the sql Quieries using both join and group funcitions
ID: 3745200 • Letter: I
Question
I am having troubles with the sql Quieries using both join and group funcitions for the following quieries.
A list of customer names with the number of orders that have ordered.
A list of all customers that have ordered a product within the last 30 days.
A list of customer name, order date, and product name ordered by the customer name.
A list of product name and the category name that they belong to.
A list of each product and the total amount that they have sold for along with their average number of units sold. Include products that have been sold.
A list of states with the number of units that have been sold to that state.
A list of month, year, and average order total for each month.
A list of mailing addresses that have at least 5 orders.
A list of the most popular products sold (consider quantity) with the number of items they have sold.
A list of product names that belong to the same category as the most popular product.
The table names are listed in the photo along with the fields withhin them.
Explanation / Answer
All joins are based the PK and FK mentioned, do follow the join conditions
-------------------------------------------------------------------------------------------------------------------------------
select
(FirstName+' '+LastName) as CustomerName, --"+" is used to concatenate strings, like-> 'A'+''+'B' will show "A B"
--here ' ' is used to put a space between Firsr name and last name
count(OrderID) -- this will count number of orders from Order table
from
CUSTOMER C
inner join --this is used to fetch common record between 2 tables using relationships
ORDER O
ON C.CustomerID=O.CustomerId
--here, CustomerId is primary key in Customer table, foreign key in Order table
--using this relation the join is performed
group by FirstName,LastName
/*
(whichever field is used in select who are NOT used in any aggregation like count(), sum() avg() etc, they should be in group by function)
--in our case we need to put first name and last name
example:
first name lastname orderId
A B 1
X Y 2
A B 3
X Y 4
M N 5
--now if we use group by on Firstname and Last name, and count on Order Id then result will look like
Name OrderCount
AB 2
XY 2
MN 5
here, Name= FirstName+LastName, OrderCount=count(OrderId)
*/
select
(FirstName+' '+LastName) as CustomerName
from
CUSTOMER C
inner join
ORDER O
ON C.CustomerID=O.CustomerId
Where OrderDate.ShipDate>dateadd(DAY,-30,getdate()) --where is used to filter data based on condition
--dateadd is a function that can be utilised to add day/month/year etc to date field
--here we have taken current date by getdate(), calculate the DAY which is 30 days earlier by -30
--dateadd(interval-day/month/year etc,
--increment-(a value like 30 or 12 etc) or decrement( -30, -12)
--fieldname-the field to which you want add certain time)
--here we have calculated the the date which is 30 days earlier then today,
--then checked all orders which have a order date later than that date
--A list of customer name, order date, and product name ordered by the customer name.
select
(FirstName+' '+LastName) as CustomerName,Orderdate.shipdate as OrderDate,ProductName
from
CUSTOMER C
inner join
ORDER O
ON C.CustomerID=O.CustomerId
inner join
orderdetail od
on o.orderid=od.orderid
inner join
product p
on p.productId=od.productid
--join order is customer->order->orderdetail->product
order by Firstname,lastname asc
--order by sorts the data based on the fields mentioned in order by clause
-- Asc means ascending order, desc means decsending order
--A list of product name and the category name that they belong to.
select distinct productname,categoryname --distinct clause eliminate duplicate results
from
product P inner join productcategory PC
on
P.categoryid=PC.categoryid
--category id is PK in Productcategory and FK in Product
--A list of each product and the total amount that they have sold for along with their average number of units sold. Include products that have been sold.
select productname,sum(Price) TotalAmount,avg(orderdetailId) AvgSales
from
OrderDetail OD inner join
Product P on P.Productid=OD.productId
group by Productname
--A list of states with the number of units that have been sold to that state.
select State,sum(quantity) UnitsSold
from Customer c
inner Order o on c.customerId=o.customerid
inner join orderdetail od on o.orderid=od.orderid
--A list of month, year, and average order total for each month.
select Year(OrderDate.ShipDate) as Year,Month(ShipDate) as Month,avg(soldQty) as AvgQuantity
from(select OrderDate.ShipDate ShipDate,sum(Quantity) soldQty from Orderdetail) as A
-----first calculate total quantity each day then on top of that use avg to calculate the average per month per year
--A list of mailing addresses that have at least 5 orders.
select Address,Count(OrderId) ordercount
from Customer c
inner Order o on c.customerId=o.customerid
inner join orderdetail od on o.orderid=od.orderid
group by Address
having Count(OrderId)>5-- having clause filters data based on aggregation used like sum(x)>10, count(y)<100 etc
--having can only be used after group by clause.
--A list of the most popular products sold (consider quantity) with the number of items they have sold.
select top 10 --select top N product
*
from
(select
Productname,sum(Quantity) qty
from Product P inner join OrderDetail od on
p.productid=od.productid
group by Productname )
A
order by
qty desc
--A list of product names that belong to the same category as the most popular product.
select * from(
select
RANK() OVER (PARTITION BY categoryname ORDER BY cnt AS 'Rank',
categoryname,Productname,qty
from
(
select
categoryname,Productname,count(orderdetailId) cnt
from Product P inner join OrderDetail od on p.productid=od.productid
productcategory pc on p.categoryid=pc.categoryid
group by Productname
)A
)b
where rank=1
-----------------------------------------
--rank function:calculates rank of a record depending on the partition by and order by clause
--example:
/*
below query, if executed in a certain dataset will yeild the result.
SELECT empno,
deptno,
sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank
FROM emp;
EMPNO DEPTNO SAL MYRANK
---------- ---------- ---------- ----------
7934 10 1300 1 -- note for each dept, rank is calculated based on salary
7782 10 2450 2 -- in our case , category name is similar dept no
7839 10 5000 3 -- sal is similar to qty and emnp no is similar to product
7369 20 800 1 -- we have filtered rank = 1 to take the most ordered product per category
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 3
7499 30 1600 4
7698 30 2850 5
/*
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.