CODEWITHSUNDEEP
mysqlselect
Navjot Singh
Navjot Singh

Reputation: 514

Get result in single query rather then three different query

Table structure and sample data

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `restaurant_id` int(11) NOT NULL,
  `bill_id` int(11) NOT NULL,
  `source_id` int(1) NOT NULL,
  `order_medium_id` int(11) NOT NULL,
  `purchase_method` varchar(255) NOT NULL,
  `totalamount` int(11) NOT NULL,
  `delivery_charg` int(11) NOT NULL,
  `discount` int(11) NOT NULL,
  `vat` int(11) NOT NULL,
  `total_price` int(11) NOT NULL DEFAULT '0',
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `source_id` (`source_id`),
  KEY `restaurant_id` (`restaurant_id`),
  KEY `bill_id` (`bill_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `customer_id`, `restaurant_id`, `bill_id`, `source_id`, `order_medium_id`, `purchase_method`, `totalamount`, `delivery_charg`, `discount`, `vat`, `total_price`, `date_created`) VALUES
(1, 1, 1, 1, 1, 0, 'cash', 1600, 0, 0, 0, 1600, '2016-05-29 13:05:40'),
(2, 2, 1, 2, 2, 1, 'cash', 1820, 0, 0, 0, 1820, '2016-06-27 07:21:25'),
(4, 1, 1, 3, 3, 0, 'cash', 1770, 0, 0, 0, 1770, '2016-05-31 13:05:56'),
(5, 3, 1, 4, 2, 1, 'cash', 1300, 0, 0, 0, 1300, '2016-06-27 07:21:31'),
(6, 1, 1, 5, 1, 0, 'cash', 950, 0, 0, 0, 950, '2016-06-02 13:06:15'),
(7, 1, 1, 6, 1, 0, 'cash', 1640, 0, 0, 0, 1640, '2016-06-03 13:06:24'),
(8, 1, 1, 7, 2, 2, 'cash', 1600, 0, 0, 0, 1600, '2016-06-27 07:21:36'),
(9, 1, 1, 8, 2, 2, 'cash', 1575, 0, 0, 0, 1575, '2016-06-27 07:21:40'),
(10, 1, 1, 9, 3, 0, 'cash', 1125, 0, 0, 0, 1125, '2016-06-06 13:06:48'),
(11, 1, 1, 10, 2, 3, 'cash', 1920, 0, 0, 0, 1920, '2016-06-27 07:21:51');

Requirement :

I want to segment records as per customer as following.

Get Rating on the basis of last purchase by customer

1. customers who ordered in last 2 week then give ratingflag 5
2. customers who ordered between 2 weeks to 4 week then give ratingflag 3
3. customers who ordered between 4 weeks to 8 week then give ratingflag 2
and so on.

Get Rating on the basis of number of order by customer

1. Customer who ordered more then 5 in a month then give rating 5  
2. Customer who ordered less then 5 and more then in a month then 4 give rating 4  
and so on.

Get Rating on the basis of total transaction by customer

1. Customer who ordered more then 5000 rs in a month then give rating 5  
2. Customer who ordered less then 5000 rs and more then in a month then 4000 give rating 4  
and so on.

Customer should be unique. We write three different query for getting records according to requirement.

I tried following . Is there any way to get result in single query. I would appreciate if you could help me with better approach of doing the same :

1.) Query for last purchase 

select o.customer_id,
       (case when max(date_created) >= date_sub(now(), interval 2 week) then 5
             when max(date_created) >= date_sub(now(), interval 4 week) then 4
             when max(date_created) >= date_sub(now(), interval 8 week) then 3
             when max(date_created) >= date_sub(now(), interval 10 week) then 2
             when max(date_created) >=  date_sub(now(), interval 12 week) then 1
        end) as rating 
from orders o where o.restaurant_id = 1
group by o.customer_id;

Output

customer_id rating
1            5
2            5
5            5

2.) Query for number of order 

select o.customer_id,
       (case when count(bill_id) >= 6 then 5
             when count(bill_id) >= 4  and count(bill_id) < 6 then 4
             when count(bill_id) >= 3  and count(bill_id) < 4 then 3
             when count(bill_id) >= 2  and count(bill_id) < 3 then 2
             when count(bill_id) >= 1 then 1
        end) as rating
from orders o where o.restaurant_id = 1
group by o.customer_id

Output

customer_id rating
1            5
2            1
5            1

3.) Query for total transaction by customer

select o.customer_id,
       (case when sum(total_price) >= 5000 then 5
             when sum(total_price) >= 3000  and sum(total_price) < 5000 then 4
             when sum(total_price) >= 2000  and sum(total_price) < 3000 then 3
             when sum(total_price) >= 1000  and sum(total_price) < 2000 then 2
             when sum(total_price) < 1000  then 1
        end) as rating
from orders o where o.restaurant_id = 1
group by o.customer_id

Output

customer_id rating
1            5
2            2
5            2

Expected Output

customer_id      R1     R2       R3
    1            5      5        5
    2            5      1        2
    3            5      1        2

Upvotes: 0

Views: 45

Answers (2)

Kaja Mydeen
Kaja Mydeen

Reputation: 585

select o.customer_id,
       (case when max(date_created) >= date_sub(now(), interval 2 week) then 5
             when max(date_created) >= date_sub(now(), interval 4 week) then 4
             when max(date_created) >= date_sub(now(), interval 8 week) then 3
             when max(date_created) >= date_sub(now(), interval 10 week) then 2
             when max(date_created) >=  date_sub(now(), interval 12 week) then 1
        end) as rating1, 
        (case when count(bill_id) >= 6 then 5
             when count(bill_id) >= 4  and count(bill_id) < 6 then 4
             when count(bill_id) >= 3  and count(bill_id) < 4 then 3
             when count(bill_id) >= 2  and count(bill_id) < 3 then 2
             when count(bill_id) >= 1 then 1
        end) as rating2,
        (case when sum(total_price) >= 5000 then 5
             when sum(total_price) >= 3000  and sum(total_price) < 5000 then 4
             when sum(total_price) >= 2000  and sum(total_price) < 3000 then 3
             when sum(total_price) >= 1000  and sum(total_price) < 2000 then 2
             when sum(total_price) < 1000  then 1
        end) as rating3
from orders o where o.restaurant_id = 1
group by o.customer_id

Try this. It is faster than above answer. No need to use joins. Check this http://sqlfiddle.com/#!9/192b0/5

Upvotes: 2

Philipp
Philipp

Reputation: 2796

You can use join on these different resultsets of your queries. http://sqlfiddle.com/#!9/192b0/3

SELECT * FROM (
select o.customer_id,
       (case when max(date_created) >= date_sub(now(), interval 2 week) then 5
             when max(date_created) >= date_sub(now(), interval 4 week) then 4
             when max(date_created) >= date_sub(now(), interval 8 week) then 3
             when max(date_created) >= date_sub(now(), interval 10 week) then 2
             when max(date_created) >=  date_sub(now(), interval 12 week) then 1
        end) as R1 
from orders o where o.restaurant_id = 1
group by o.customer_id) AS lastPurchase
LEFT JOIN 
(
select o.customer_id,
       (case when count(bill_id) >= 6 then 5
             when count(bill_id) >= 4  and count(bill_id) < 6 then 4
             when count(bill_id) >= 3  and count(bill_id) < 4 then 3
             when count(bill_id) >= 2  and count(bill_id) < 3 then 2
             when count(bill_id) >= 1 then 1
        end) as R2
from orders o where o.restaurant_id = 1
group by o.customer_id
) AS orderQuery USING(customer_id)
LEFT JOIN
(
select o.customer_id,
       (case when sum(total_price) >= 5000 then 5
             when sum(total_price) >= 3000  and sum(total_price) < 5000 then 4
             when sum(total_price) >= 2000  and sum(total_price) < 3000 then 3
             when sum(total_price) >= 1000  and sum(total_price) < 2000 then 2
             when sum(total_price) < 1000  then 1
        end) as R3
from orders o where o.restaurant_id = 1
group by o.customer_id
)  AS totalTransactions  USING(customer_id)

Upvotes: 2

Related Questions