GROUP BY : EXAMPLE : List slTrip.CaptainID, TripMonth, and TripCnt for each Capt
ID: 3878950 • Letter: G
Question
GROUP BY :
EXAMPLE :
List slTrip.CaptainID, TripMonth, and TripCnt for each CaptainID in slTrip by TripMonth.
Produce the values for and group by the attribute TripMonth using the MONTH() function for slTrip.TripStart.
Order the result first by slTrip.CaptainID and then by TripMonth both in ascending order.
ANSWER:
SELECT slTrip.CaptainID, month ( tripStart) AS tripmonth, COUNT(*) as tripcnt
FROM slTrip
GROUP by slTrip.CaptainID, month ( tripStart )
ORDER by CaptainID, tripmonth
1) Schema : SuppCnt
Compute the count of repairs for each supplier in slRepair.
List slRepair.SupplierID and SuppCnt for all repairs.
SuppCnt is the count of repairs for a given supplier.
Order the list by SuppCnt in descending order.
2) schema : SuppCnt2011
Compute the count of repairs for each supplier in slRepair for the year 2011.
List slRepair.SupplierID and SuppCnt for repairs for the year 2011.
SuppCnt is the count of repairs for a given supplier.
Order the list by SuppCnt in descending order.
3) schema: TripPaxCnt
Count the number of trips taken by each passenger in slCensus. slCensus documents the passengers on a trip.
List slCensus.PaxID and TripCnt for all passengers (PaxID) in slCensus.
TripCnt is the count for each PaxID in slCensus.
Order the list by TripCnt in descending order.
4) Schema : TripPaxCnt1
Count the number of trips taken by each passenger in slCensus where the passenger was listed as the first passenger.
List slCensus.PaxID and TripCnt for all passengers (PaxID) in slCensus for passengers listed as PaxOrder = 1.
Order the list by TripCnt in descending order.
Order the list by TripCnt in descending order.
5) schema : AvgGearSupp
List slGearPurchase.SupplierID and AVGamount for each supplier in slGearPurchase.
AVGamount is the AVG() of slGearPurchase.PurchaseAmount for each supplier.
Order the list by AVGamount descending.
6) schema : AvgGearSuppIN()
List slGearPurchase.SupplierID and AVGamount for each supplier in slGearPurchase for SupplierIDs 1513, 1518, 1511, and 1507.
AVGamount is the AVG() of slGearPurchase.PurchaseAmount for each supplier listed.
Use IN() to create an enumerated list of SupplierIDs.
Order the list by AVGamount descending.
7) Sche,ma : CaptBYMonth
List slTrip.CaptainID, slTrip.LaunchID, and TripCnt for each CaptainID in slTrip by LaunchID where the water temperature is greater than 75.
TripCnt is the count of trips for each captain by LaunchID.
Order the list first by slTrip.CaptainID and then by slTrip.LaunchID both in ascending order.
9) AvgGasYear
List GasYear and AvgGas for all gas purchases.
Derive the values for and group by GasYear using the YEAR() function for slGasPurchase.PurchaseDate.
AvgGas is the AVG() of slGasPurchase.CostPerGallon for each year in the resulting list.
Order the list by GasYear in ascending order.
Note: yes, there is an error in the data with the year 2103. These data are based on an actual ships log and there are many errors...
10) Schema : AvgGasYear10
List GasMonth and AvgGas for all gas purchases in the year 2010.
Derive the values for and group by GasMonth using the MONTH() function for slGasPurchase.PurchaseDate.
AvgGas is the AVG() of slGasPurchase.CostPerGallon for each month in the resulting list.
Order the list by GasMonth in ascending order.
11) Schema : TripDOW
List WeekDay and DayCnt for all trips in slTrip.
Derive values for and group by WeekDay using the DATEPART() function with the parameter interval WEEKDAY for slTrip.TripStart.
DayCnt is the count of the number of trips for each day of the week.
Order the list by DayCnt in descending order.
WEEKDAY interval: DATEPART() returns an integer from 1 to 7. The weekday numbering starts on Sunday so Sunday = 1 and Saturday = 7.
Looks like most of the trips were on the weekend...
Explanation / Answer
Below are the queries. SDince do datasets are given hence it is not possible to give any output. Looks like point 7 is missing the temperate attribute.
1. select slRepair.SupplierID ,count(*) AS SuppCnt
from slRepair
group by slRepair.SupplierID
order by SuppCnt desc;
2.select slRepair.SupplierID ,count(*) AS SuppCnt
from slRepair
group by slRepair.SupplierID
order by SuppCnt desc;
3.select slCensus.PaxID ,count(*) AS TripCnt
from TripPaxCnt
group by slCensus.PaxID
order by TripCnt desc;
4.select slCensus.PaxID ,count(*) AS TripCnt
from TripPaxCnt1
where PaxOrder = 1
group by slCensus.PaxID
order by TripCnt desc;
5.select slGearPurchase.SupplierID ,
AVG(slGearPurchase.PurchaseAmount) AS AVGamount
from slGearPurchase
group by slGearPurchase.SupplierID
order by AVGamount desc;
6.select slGearPurchase.SupplierID ,
AVG(slGearPurchase.PurchaseAmount) AS AVGamount
from slGearPurchase
where slGearPurchase.SupplierID in (1513, 1518, 1511, 1507)
group by slGearPurchase.SupplierID
order by AVGamount desc;
7.select slTrip.CaptainID, slTrip.LaunchID, count(*) AS TripCnt
from slTrip
where temp_water > 75
group by slTrip.CaptainID, slTrip.LaunchID
order by slTrip.CaptainID, slTrip.LaunchID;
9.select YEAR(slGasPurchase.PurchaseDate) AS GasYear,
AVG(slGasPurchase.CostPerGallon) AS AvgGas
from slGasPurchase
group by YEAR(slGasPurchase.PurchaseDate)
order by GasYear;
10.select MONTH(slGasPurchase.PurchaseDate) AS GasMonth,
AVG(slGasPurchase.CostPerGallon) AS AvgGas
from slGasPurchase
group by MONTH(slGasPurchase.PurchaseDate)
order by GasMonth;
11.select DATEPART(weekday,slTrip.TripStart) AS WeekDay,count(*) AS DayCnt
from slTrip
group by DATEPART(weekday,slTrip.TripStart)
order by DayCnt desc;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.