| Author |
Topic |
|
krisdmd
Starting Member
9 Posts |
 Posted - 2015-03-10 : 15:04:51
|
Hello,
I'm stuck with building a query.
I need to select for each year, the shipment company which has the biggest freight cost and the total amount of shipment costs for each year.
Well i'm stuck at grouping per year and how i can get the total costs of all freight costs for that company with the biggest freight cost.
SELECT YEAR(o.OrderDate) ShowYear, MAX(o.Freight) ShipmentCosts, o.ShipVia, s.CompanyName FROM Orders o
LEFT JOIN Shippers s ON (o.ShipVia=s.ShipperID)
GROUP BY o.ShipVia, s.CompanyName, YEAR(o.OrderDate), o.Freight
ORDER BY ShipmentCosts DESC
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-10 : 15:43:33
|
not sure what you mean by "stuck at grouping per year".
Can you provide some sample data for both tables, show what you're getting with your query and what you want to get?
I see o.Freight in your group by, that shouldn't be there. |
 |
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-11 : 15:47:48
|
When i use this query =>
SELECT Distinct YEAR(o.OrderDate) Jaar, MAX(o.Freight) ShipmentCosts, s.CompanyName FROM Orders o
INNER JOIN Shippers s ON (o.ShipVia=s.ShipperID)
GROUP BY YEAR(o.OrderDate), s.CompanyName
It's not grouping the year. It shows 3 items for each year. i need unique years.
|
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-11 : 15:59:06
|
1. remove Distinct
2. It groups by customer name within year, the way it is written
3. post some sample output.
|
|
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-11 : 16:11:10
|
Sample =>
1996 360,63 Federal Shipping
1997 1007,64 Federal Shipping
1998 606,19 Federal Shipping
1996 214,27 Speedy Express
1997 458,78 Speedy Express
1998 411,88 Speedy Express
1996 890,78 United Package
1997 810,05 United Package
1998 830,75 United Package |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-11 : 16:18:16
|
So, taking 1998 as an example, if you only want to see one row, which customer should it display? Federal Shipping, Speedy Express or United Package?
|
|
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-11 : 16:42:43
|
| It should only show the one with the maximum price so for 1998 united package |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2015-03-11 : 17:58:12
|
select *
(
SELECT YEAR(o.OrderDate) as ShowYear, o.Freight as ShipmentCosts, o.ShipVia, s.CompanyName ,row_number() over (Partition by YEAR(o.OrderDate) order by o.freight desc) as rowid
FROM Orders o
LEFT JOIN
Shippers s ON (o.ShipVia=s.ShipperID)
) aa
where
aa.rowid = 1
Vincent Senese
\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-11 : 18:33:38
|
simple:
SELECT YEAR(o.OrderDate) Jaar, MAX(o.Freight) ShipmentCosts, s.CompanyName FROM Orders o
INNER JOIN Shippers s ON (o.ShipVia=s.ShipperID)
GROUP BY YEAR(o.OrderDate), s.CompanyName
having o.freight = max(o.freight)
|
|
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-12 : 15:58:07
|
Having this error gbritton
Msg 8121, Level 16, State 1, Line 64
Column 'Orders.Freight' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
|
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-12 : 16:01:12
|
quote:
Originally posted by krisdmd
Having this error gbritton
Msg 8121, Level 16, State 1, Line 64
Column 'Orders.Freight' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Since there is no 'Orders.Freight' in the query I sent you, please post the whole query that gives this error |
|
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-12 : 16:04:56
|
| It's a ms sql. The above queries didn't worked. |
|
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-12 : 16:28:57
|
I'm using this query =>
SELECT YEAR(o.OrderDate) Jaar, MAX(o.Freight) ShipmentCosts, s.CompanyName FROM Orders o
INNER JOIN Shippers s ON (o.ShipVia=s.ShipperID)
GROUP BY YEAR(o.OrderDate), s.CompanyName
having o.freight = MAX(o.freight) |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-12 : 16:31:15
|
quote:
Originally posted by krisdmd
It's a ms sql. The above queries didn't worked.
Yes, this is all ms sql, I said to try:
SELECT YEAR(o.OrderDate) Jaar, MAX(o.Freight) ShipmentCosts, s.CompanyName FROM Orders o
INNER JOIN Shippers s ON (o.ShipVia=s.ShipperID)
GROUP BY YEAR(o.OrderDate), s.CompanyName
having o.freight = max(o.freight)
you replied that you got the error:
quote:
Msg 8121, Level 16, State 1, Line 64
Column 'Orders.Freight' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
however there is no Orders.Freight column in the code I posted. Therefore you modified my query to include a reference to Orders.Freight in the HAVING clause. That is why I want you to post the exact query you used that generated the error. |
|
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-12 : 16:39:13
|
i don't know why sql management studio is showing this error. i'm using your query.
it changes o.Freight in Orders.Freight in the error message. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-12 : 16:45:22
|
Sorry, my bad. That's a SQL anomaly (which I forgot about!). Have a go with this one:
with cte as
(
SELECT YEAR(o.OrderDate) Jaar, o.Freight, s.CompanyName
, max(o.Freight) over(partition by YEAR(o.OrderDate), o.ShipVia) as ShipmentCosts
FROM Orders o
INNER JOIN Shippers s ON o.ShipVia=s.ShipperID
)
select Jaar, ShipmentCosts, CompanyName
From cte
where Freight = ShipmentCosts
btw what if there is a tie? |
|
|
|
krisdmd
Starting Member
9 Posts |
Posted - 2015-03-12 : 16:51:13
|
Well it's for my school. This is the only query i was stuck. I didn't know it was that difficult to create it.
We haven't seen with {} yet in our lessons.
Thanks alot for the help. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-12 : 21:08:16
|
the with is just a convenience. You can rewrite it like this:
select Jaar, ShipmentCosts, CompanyName
from
(
SELECT YEAR(o.OrderDate) Jaar, o.Freight, s.CompanyName
, max(o.Freight) over(partition by YEAR(o.OrderDate), o.ShipVia) as ShipmentCosts
FROM Orders o
INNER JOIN Shippers s ON o.ShipVia=s.ShipperID
) q
where Freight = ShipmentCosts |
|
|
|
dbgajendrayadav
Starting Member
1 Post |
Posted - 2015-03-18 : 05:22:41
|
Hi,
This can give you the required result....
Select YM.OrderDate, YM.Freight, C.CompanyName
from dbo.Orders YM
Inner Join
(
Select Year(OrderDate) Odt, MAX(Freight) FreightAmt from dbo.Orders Y
group By Year(OrderDate)
) X
ON YEAR(YM.OrderDate) = X.Odt and YM.Freight = X.FreightAmt
Left Join dbo.Shippers C
ON YM.ShipVia = C.ShipperID |
|
|
|
|
Group BY and Get total