SQL Query to Retrieve data by comparing column name to a particular string

Question

I have a SQL Table consisting of customer, transaction and store. Store has 3 values (X,Y,Z)

I want to retrieve customers who shopped at particular store, so I used this query

select distinct customer from TABLE group by store.

However, now I want the customer details who shopped at 2 stores (X,Y) (Y,Z) (Z,X) and also (X,Y,Z).

When I use

select distinct customer from TABLE where store='X'

it gives 0 results in oracle SQL Developer

How to proceed from here?

This is the actual code im using in SQL developer. I copy pasted it — user2133404
– user2133404, Commented Mar 6, 2013 at 4:23
I wonder how you save stores in the table, in one column? If so, what the actual value looks like when it has more than one store. And in this case you might want to use where store like '%X%' — spiritwalker
– spiritwalker, Commented Mar 6, 2013 at 4:30
ok store the string in a varchar and check it with store=varcharVariable — user2166576
– user2166576, Commented Mar 6, 2013 at 4:30
@spiritwalker This is the sample data STORE CUSTOMER TRANSACTION X 4567 66666 X 4567 55555 X 2233 43216 Y 2345 67890 Y 4567 23456` — user2133404
– user2133404, Commented Mar 6, 2013 at 4:35

Ken Clark · Accepted Answer · 2013-03-06 11:28:44Z

1

Try following:

   Select Customer From
    (
         Select Customer,Store from TableName group by Store,Customer
    )tbl
    Group By Customer Having COUNT(Customer)>=2 Order By Customer

Edit:

Declare @MainTable table
(
  Customer varchar(222),Store varchar(2222)
)
Insert Into @MainTable
Select 'C1','X'
Union All
Select 'C1','Y'
Union All
Select 'C1','X'
Union All
Select 'C2','X'
Union All
Select 'C2','Y'
Union All
Select 'C2','Z'
Union All
Select 'C3','X'
Union All
Select 'C3','Z'
Union All
Select 'C4','X'
Union All
Select 'C4','Y'


Declare @temp table 
(
  Customer varchar(200)
)
Insert Into @temp 
Select Customer From
    (
         Select Customer,Store from @MainTable group by Store,Customer
    )tbl
Group By Customer Having COUNT(Customer)>=2 Order By Customer


Declare @Customer_Store table 
(
  Customer varchar(200),
  Stores varchar(200)
)

DECLARE @Stores varchar(10)
Declare @Customer varchar(256)
While((Select COUNT(*) From @temp)>0)
Begin
        Set @Customer=(Select Top 1 Customer From @temp)
        Select @Stores=coalesce(@Stores + ',','') + Store From 
        @MainTable Where Customer=@Customer
        Group By Store
        Order By Store

        Insert Into @Customer_Store Select @Customer,@Stores

        Delete From @temp Where Customer=@Customer
        Set @Stores=null
End


Select Cast(COUNT(Customer) as Varchar(5))+' Customers shopped at Store ('+Stores+')'          CustomerDetail From @Customer_Store
Group By Stores

Output:

2 Customers shopped at Store (X,Y)
1 Customers shopped at Store (X,Y,Z)
1 Customers shopped at Store (X,Z)

edited Mar 6, 2013 at 11:28

answered Mar 6, 2013 at 4:50

Ken Clark

2,55017 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2133404 Over a year ago

Thanks, i think it worked. I got customers who shopped at 2 or more stores. But I want to know the values separately like, n no of customers shopped at (X,Y) m at (Y,Z) and p at all three. Is there any way?

Ken Clark Over a year ago

I have edited answer above and have included sample code to your problem. Check if this works for you.

David Aldridge Over a year ago

You're grouping by the customer and counting them?

Ken Clark Over a year ago

query will return the number of customers shopped at different stores and has shopped at more than one store.

Dhanya Raj · Accepted Answer · 2013-03-06 06:42:52Z

0

select distinct customer from tablename group by customer ,store having count(stores)>2

sorry....you can try like this

"select customer,store from table_name group by customer ,store having count(customer)>=1 order by customer asc"

i hope this is correct.

edited Mar 6, 2013 at 6:42

answered Mar 6, 2013 at 5:07

Dhanya Raj

1171 silver badge8 bronze badges

4 Comments

user2133404 Over a year ago

Nope, its giving all the rows including those who shop at single store

Dhanya Raj Over a year ago

now i have edited the query just try it out like this,. select customer,store from table_name group by customer ,store having count(customer)>=1 order by customer asc

user2133404 Over a year ago

Its still returning all the rows.

Dhanya Raj Over a year ago

i am working on sqlserver 2008....just check this..select customer,trans,store from table_name group by customer ,trans,store having count(customer)>=1 order by customer asc

valex · Accepted Answer · 2013-03-06 07:23:10Z

0

I think you should use something like GROUP_CONCAT in MySQL.

Here you can find how you can emulate it in Oracle

For example for Oracle 11g R2 you can use LISTAGG:

SQLFiddle demo

SELECT customer, 
       LISTAGG(store, ',') WITHIN GROUP (ORDER BY store) 
       AS STORES
FROM   
(select distinct customer,store from t) t1
GROUP BY customer;

answered Mar 6, 2013 at 7:23

valex

24.2k7 gold badges47 silver badges62 bronze badges

Collectives™ on Stack Overflow

SQL Query to Retrieve data by comparing column name to a particular string

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related