Group BY and Get total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Group BY and Get total

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources