Help Needed in Grouping Logic

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  May 18, 2018 at 2:22 pm

  #354475

  Declare @UserTransaction table(IdUser int, IdShop int);

  insert into @UserTransaction
select 100, 1 union all
select 100, 2 union all
select 100, 1 union all
select 101, 1 union all
select 102, 2 union all
select 102, 2 union all
select 102, 3 union all
select 103, 1 union all
select 103, 1

  Declare @shop-2 table(IdShop int, shopname varchar(100));

  insert into @shop-2

  select 1, 'Apple' union all
select 2, 'Google' union all
select 3,'Microsoft' union all
select 4, 'IBM'

  Expected Result:
  select 100 as IdUser,'Shopped at More than one shop' as ShopDetails union all
select 101 as IdUser,'Shopped at only one shop' as ShopDetails union all
select 102 as IdUser,'Shopped at More than one shop' as ShopDetails union all
select 103 as IdUser,'Shopped at only one shop' as ShopDetails

  my try:
  ;with data as (
select iduser, ROW_NUMBER() over(partition by iduser,idshop order by iduser)as rn from @UserTransaction )

  select iduser, case when MAX(rn) > 1 then 'Shopped at More than one shop' else 'Shopped at only one shop' end as ShopDetails from data group by iduser

  Basically i need to check uset shopped at more than one shop and shop should be different then show message as Shopped at More than one shop else show message as
  Shopped at only one shop.

  I tried with row_number with partition but giving wrong result. Any sample query to achieve this.
  

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  May 18, 2018 at 2:40 pm

  #1991086

  You could use a CASE statement instead of the IIF... but the COUNT(DISTINCT()) solves your problem. May not scale well, though.

  SELECT s.IdUser
    , IIF(s.UniqueShops = 1,'One shop','Multiple shops') AS Msg
FROM
(SELECT ut.IdUser, COUNT(DISTINCT(ut.IdShop)) AS UniqueShops
FROM @UserTransaction ut
GROUP BY ut.IdUser) s;

• ScottPletcher

  SSC Guru

  Points: 101103

  More actions

  May 18, 2018 at 2:54 pm

  #1991089

  
SELECT ut.IdUser,
    MIN(ut.IdShop) AS IdShop_Min, MAX(ut.IdShop) AS Id_Shop_Max /*MIN & MAX are optional, of course*/
FROM @UserTransaction ut
GROUP BY ut.IdUser
HAVING COUNT(DISTINCT ut.IdShop) > 1
ORDER BY ut.IdUser

  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".

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  May 19, 2018 at 1:46 am

  #1991109

  Pitching in, this is the simplest way I could come up with
  😎

  
USE TEEST;
GO
SET NOCOUNT ON;

  Declare @UserTransaction table(IdUser int, IdShop int);

  insert into @UserTransaction
select 100, 1 union all
select 100, 2 union all
select 100, 1 union all
select 101, 1 union all
select 102, 2 union all
select 102, 2 union all
select 102, 3 union all
select 103, 1 union all
select 103, 1 ;

  Declare @shop-2 table(IdShop int, shopname varchar(100));

  SELECT
  UT.IdUser
 ,CASE COUNT(DISTINCT UT.IdShop)
   WHEN 1 THEN 'Shopped at only one shop'
   ELSE   'Shopped at More than one shop'
  END AS ShopDetails
FROM   @UserTransaction   UT
GROUP BY UT.IdUser;

  Output
  

  IdUser ShopDetails
------- -----------------------------
100  Shopped at More than one shop
101  Shopped at only one shop
102  Shopped at More than one shop
103  Shopped at only one shop

  

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  May 21, 2018 at 7:58 am

  #1991225

  Thank you guys for the reply and have one  tiny question. 
  Is there any way to include the shop name when shopped only at one shop, expected answer:
  select 100 as IdUser,'Shopped at More than one shop' as ShopDetails union all
select 101 as IdUser,'Shopped at Apple' as ShopDetails union all
select 102 as IdUser,'Shopped at More than one shop' as ShopDetails union all
select 103 as IdUser,'Shopped at Apple' as ShopDetails

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  May 21, 2018 at 8:20 am

  #1991230

  This should do
  😎

  USE TEEST;

  GO

  SET NOCOUNT ON;

  Declare @UserTransaction table(IdUser int, IdShop int);

  insert into @UserTransaction

  select 100, 1 union all

  select 100, 2 union all

  select 100, 1 union all

  select 101, 1 union all

  select 102, 2 union all

  select 102, 2 union all

  select 102, 3 union all

  select 103, 1 union all

  select 103, 1 ;

  Declare @shop-2 table(IdShop int, shopname varchar(100));

  insert into @shop-2

  select 1, 'Apple' union all

  select 2, 'Google' union all

  select 3, 'Microsoft' union all

  select 4, 'IBM'

  SELECT

  UT.IdUser

  ,CASE COUNT(DISTINCT UT.IdShop)

    WHEN 1 THEN 'Shopped at ' + MAX(SH.shopname)

    ELSE  'Shopped at More than one shop'

  END AS ShopDetails

  FROM   @UserTransaction UT

  INNER JOIN @shop-2     SH

  ON    UT.IdShop  = SH.IdShop

  GROUP BY UT.IdUser;

  Output
  

  IdUser ShopDetails
------ -----------------------------
100    Shopped at More than one shop
101    Shopped at Apple
102    Shopped at More than one shop
103    Shopped at Apple

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  May 21, 2018 at 8:38 am

  #1991237

  Thank you Eric and wonderful. i did not know that we can apply MAX operator for  strings. Good learning today. appreciated your swift response.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  May 21, 2018 at 8:52 am

  #1991243

  KGJ-Dev - Monday, May 21, 2018 8:38 AM

  Thank you Eric and wonderful. i did not know that we can apply MAX operator for  strings. Good learning today. appreciated your swift response.

  You are very welcome
  😎