GROUP BY :

EXAMPLE :

Schema:CaptByMonth

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...