Two 'chains'.

I should be silent in the mean time. I'm nothing close to an expert. I have way too many newb questions. I have reasons for not being affraid of algorithms...

Reply noted. I'll study as soon I've settled down a bit. Thanks for your effort in advance.

Not a biggy. Thanks for the help. I think Jeff is still researching. So let's not close this case yet.

No I didn't.

Like stated before, the inner join is not be removed. It represents a real life situation. I've only skinned it down for this forum. Call it respect 🙂

But...

Ok, keeping the thread alive :).

It's not as easy it looks I think. My theory for now is that I need to accept the performance. I hope you'll come up...

http://www.sqlservercentral.com/Forums/Attachment9832.aspx

What I have posted is a simplification of the real case. I can't post the real stuff as it owned by the company. But the real case isn't returning much...

Oki, so I added a where clause. The execution plan still shows 39% table scan on the Sales_City table.

The final query looks like this then:

select

Sold_To_Country,

sum(Sales_New_York) Sales_New_York,

sum(Sales_Detroit) Sales_Detroit,

sum(Sales_Los_Angeles) Sales_Los_Angeles,

sum(Sales_Paris) Sales_Paris,

sum(Sales_Lyon)...

Funny, has about the same execution speed as my original query. have to deal with the fact that implementing CASE/WHEN statements like this implies a serious performance hit. It forces...

CREATE TABLE [dbo].[CountriesInvolved](

[Sold_To_Country] [nvarchar](255) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[Sales_City](

[Sold_To_Country] [nvarchar](255) NULL,

[ToCity] [nvarchar](255) NULL,

[SalesAmount] [float] NULL

) ON [PRIMARY]

Well I'll be dammed. The first query is faster then the more readable and shorter query...

I've tested it using 19 million records.

select

Sold_To_Country,

sum(Sales_New_York) Sales_New_York,

sum(Sales_Detroit) Sales_Detroit,

sum(Sales_Los_Angeles) Sales_Los_Angeles,

sum(Sales_Paris) Sales_Paris,

sum(Sales_Lyon) Sales_Lyon,

sum(Sales_Bonn) Sales_Bonn,

sum(Sales_Hamburg) Sales_Hamburg,

sum(Sales_Frankfurt)...

Yes, they're in the Sales_City table! In the process of making screenies I forgot to post the answer.

Jeff Moden (9/15/2011)

---

Also, if your table has more cities in it than what you have in the query, add the following WHERE clause just before the GROUP BY...

...

It's not the desired result. Here's the deal:

Using this query:

select

b.Sold_To_Country,

...