-1

I have a table with customers (fixed number of customers). These customers purchages goods either online or by visiting the shop.

I want to get the list of all the customers with an additional column stating oredered. Fill the column with (If cutomer ordered online) 1 or (if not oredered online) 0.

Each customer can have more orders (via telefone, via email or via visiting the store). the column online order has X mark if ordered online for that partcular order the customer.

I have orders table and customers table, identifier is the customer ID. In orders table there is a column that is marked with X. I need to convert the X to 1 and the other with 0 somehow.

My table looks like:

Customer ID| Order ID| Order 1 |1 | - 1 |2 | 1 |3 |X 2 |4 | - 2 |5 | - 2 |6 | - 3 |7 |X 3 |8 |X 3 |9 |X

Resulted table should look like:

|Customer ID|Oredered Online |Customer1 |1 |Customer2 |0 |Customer3 |1

I dont know exactly the conditions work, i tried CASE, WHEN THEN, did'nt work as i wanted.

Thank you!

sveer
  • 427
  • 3
  • 16
  • 2
    Are you using MySQL, SQLite or Postgresql? Don't tag products not involved. – jarlh Mar 06 '17 at 14:44
  • 1
    @Sai What do your source tables look like? Are there are only two, one for customers and one for orders? How would you identify an online order in the source tables? – Jeremy Real Mar 06 '17 at 14:45
  • 1
    Show what you have tried so far. – Jiri Tousek Mar 06 '17 at 14:46
  • Use `max(OrderedOnline)` by customer to get the relevant information – Shadow Mar 06 '17 at 14:47
  • @Jeremy Thanks for the question, I will update the text. – sveer Mar 06 '17 at 14:47
  • @Jiri I asked here a question after trying every possible method i found. once i made sure that i couldnt get the result what i wanted, i posted here for possible solutions. I tried this thread: http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select – sveer Mar 06 '17 at 15:05
  • @Sai It seems like a case statement should work. Can you tell me what is the datatype of the Order column? Are the '-' characters in the column null values? – Jeremy Real Mar 06 '17 at 15:38
  • `-` (its minus) means the person didnt came to shop. ` ` means he was personally at shop. `X` means he ordered good online. – sveer Mar 06 '17 at 15:55
  • @Sai In that case, the datatype must be text. I would export some data from orders and check whether there are space characters in that field that are messing up your CASE statement. – Jeremy Real Mar 06 '17 at 16:02

2 Answers2

1

You could use a not in clause on subselect

Customer that order only in shop 

select customer_id from my_table 
where customer_id not (select customer_id 
              from my_table where ordered_online = 1)

Customer that order only online

select customer_id from my_table 
where customer_id not (select customer_id 
            from my_table where ordered_online = 0)

if you need i the same select you could use 

select distinct  t1.customer_id , t2.check as only_shop,  t3.check as only_online, 
from my_table t1
left join (
    select distinct customer_id, 'X' as check  from my_table 
    where customer_id not (select customer_id 
                  from my_table where ordered_online = 1)
) t2 on t1.customer_id = t2.customer_id
left join (
    select distinct customer_id, 'X' as check from my_table 
    where customer_id not (select customer_id 
                from my_table where ordered_online = 0)
) t3 on  t1.customer_id = t3.customer_id

looking to your sample should be 

  select distinct customer_id, 1 as ordered_on_line  from my_table 
      where customer_id not (select customer_id 
                    from my_table where ordered_online = 1)
  union 
  select distinct customer_id, 0  from my_table 
      where customer_id not (select customer_id 
                  from my_table where ordered_online = 0)
  order by customer_id
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • I would like to have a combined result. online orders with an extra column next to customer id. filled with `1`s and `0`s – sveer Mar 06 '17 at 15:01
  • I am sorry, I added an example table and required result to my question. could you please give a look? Thanks – sveer Mar 06 '17 at 15:17
  • Why do you select customer_id where oredered_online = 1 / =0, I have X, - or empty. you are suggesting to select the customer ids that are not in either of the the tables. I am really sorry, didn't get the logic. But the idea of `1 as` and `union` seems appealing. – sveer Mar 06 '17 at 15:34
  • you have asked i your question of show 1 or 0 ..." need to convert the X to 1 and the other with 0.. " ... seems you have ot the " ... it seems to me that you have no clear ideas and that is changing the questions with each answer .. – ScaisEdge Mar 06 '17 at 15:40
  • Thanks for your feedback! i am sorry for giving you such an impression. I am just trying to find a solution. – sveer Mar 06 '17 at 16:02
  • 1
    Using the two separated select in union the query provide in the first select the customer that order not online and in the second the customer that ordered online .. so for each of these the select return also 1 or 0 as ordered_online_column .. and last .. the result is order by customer id for a better reading .. hope this clear my answer .. – ScaisEdge Mar 06 '17 at 16:07
0

I would expect this to work. If it doesn't, there may be some issue with the data in the order column. Perhaps spaces or another character you're not seeing. 

select customer_id
      ,order_number
      ,case when order = 'x' then 1
            else 0 end as ordered_online
from orders
Jeremy Real
  • 726
  • 1
  • 6
  • 11
  • Thanks Jeremy! I will have access to db again in 2 days, i will let you know my results. – sveer Mar 07 '17 at 10:21