SQL query urgent pls help

  • Thread starter Thread starter subs
  • Start date Start date
S

subs

ocity dcity consignee ship date Total Price Total ships week no
memphis oasis NAM1 2/21/2008 333
memphis oasis NAM1 2/20/2008 3333
mumbai cinci1 NAM3 2/23/2009 34
india cinci NAM2 1/1/2009 222
india cinci NAM2 1/11/2008 3333
mumbai delhi NAM3 6/1/2009 3259
mumbai delhi NAM3 6/5/2009 9444

I want to add up the total price when the ocity, dicity , consignee
are same and the ship date belongs to the same week in the same year,
and also fill up the total ships and week no. So i need a SQL query
which gives
the following result

ocity dcity consignee ship date Total Price total ships weekno
memphis oasis NAM1 2/20/2008 3666 2
india cinci NAM2 1/1/2009 3555 2
mumbai delhi NAM3 6/1/2009 12703 2

I appreciate any help in SQL query. Thanks you so much
 
SELECT ocity, dcity, consignee, Year([ship Date]) as TheYear,
Datepart("ww",[Ship date]) as TheWeek
, Sum([Total Price] as TtlPrice, Count(oCity) as TotalShipments

FROM [someTable]
GROUP BY ocity, dcity, consignee, Year([ship Date])
, Datepart("ww",[Ship date])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
ocity dcity consignee ship date Total Price Total ships week no
memphis oasis NAM1 2/21/2008 333
memphis oasis NAM1 2/20/2008 3333
mumbai cinci1 NAM3 2/23/2009 34
india cinci NAM2 1/1/2009 222
india cinci NAM2 1/11/2008 3333
mumbai delhi NAM3 6/1/2009 3259
mumbai delhi NAM3 6/5/2009 9444

I want to add up the total price when the ocity, dicity , consignee
are same and the ship date belongs to the same week in the same year,
and also fill up the total ships and week no. So i need a SQL query
which gives
the following result

ocity dcity consignee ship date Total Price total ships weekno
memphis oasis NAM1 2/20/2008 3666 2
india cinci NAM2 1/1/2009 3555 2
mumbai delhi NAM3 6/1/2009 12703 2

I appreciate any help in SQL query. Thanks you so much
Click to expand...

Create two calculated fields, ShipYear: Year([Ship date]) and ShipWeek:
DatePart("ww", [Ship date]). Change the query to a Totals query, grouping by
ocity, dcity, consignee, ShipYear and ShipWeek; use Count on the primary key
of the table to count total ships, Sum on total price.

The SQL would be

SELECT ocity, dcity, consignee, First([ship date] AS FirstOfShipDate,
Sum([Total Price]) AS GrandTotalPrice, Count(*) AS [Total Ships],
DatePart("ww", ShipDate) AS Weekno
FROM yourtable
GROUP BY ocity, dcity, consignee, Year([shipdate]), DatePart("ww", [ship
date]);
 
Back
Top