September 4, 2019 at 7:55 am
Hi Friends,
Need help building query. I have below data.
Item Name Delivered country
TV 32 inch USA
TV 32 inch UK
Tv 32 inch India
Tv 40 Inc India
Tv 50 India
Tv 50 UK
If the Item is delivered in more than one country i should be consider as Global. I am looking for the below output.
Item Name Delivered country
TV 32 inch Global
Tv 40 Inc India
Tv 50 Global
Regards
Prakash
September 4, 2019 at 8:32 am
What have you tried?
A few things to think about. When you note that something is in more than one country, you're considering that it has appeared in more than one row. You might look at a COUNT(*) aggregate for the items. Then you can replace the value of the country with the word "Global'
Hint, use a CTE:
with mycte
as
(select * from mytable)
select *
from mytable
inne join mycte
on mytable.pk = mycte.pk
September 4, 2019 at 2:45 pm
What have you tried?
A few things to think about. When you note that something is in more than one country, you're considering that it has appeared in more than one row. You might look at a COUNT(*) aggregate for the items. Then you can replace the value of the country with the word "Global'
Hint, use a CTE:
with mycte
as
(select * from mytable)
select *
from mytable
inne join mycte
on mytable.pk = mycte.pk
I don't think that you really need to use a CTE, but you certainly don't want to use a join. You only need one scan of the table and joining would add an unnecessary scan.
Also, unless item/delivered country is a unique key I would use a distinct count instead of a non-distinct count. (An alternate version would be to compare the MIN() and MAX() values.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 4, 2019 at 6:21 pm
I guessed you would want to sort/order by Item first; if not, you might want to reverse the GROUP BY to Name, Item.
SELECT Item, Name,
CASE WHEN COUNT(*) = 1 THEN MAX([Delivered country]) ELSE 'Global' END AS [Delievered Country]
FROM dbo.table_name
GROUP BY Item, Name
ORDER BY Item, Name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply