CODEWITHSUNDEEP
mysqlcase
Santosh Achari
Santosh Achari

Reputation: 3006

Using MySQL Case for comparing dates

I need to tabulate the data from three tables for a report. Please see this query

SELECT
    DATE( sale.sale_time ) AS dat,
    location.location_name as location,
    sale.sale_id AS total_orders, 
    SUM( CASE sale.is_cancelled WHEN 0 THEN sale.sale_amount   ELSE 0 END ) AS sales, 
    SUM( CASE sale.is_cancelled WHEN 0 THEN sale.sale_discount ELSE 0 END ) AS discounts,
    SUM( CASE sale.is_cancelled WHEN 0 THEN sale.sale_tax      ELSE 0 END ) AS taxes, 
    COUNT( DISTINCT sale.customer_id ) AS total_customers, 
    SUM( CASE WHEN DATE( person.added_on ) = DATE( sale.sale_time ) THEN 1 ELSE 0 END ) AS new_customers,
    SUM( CASE sale.is_cancelled WHEN 1 THEN 1 ELSE 0 END ) AS cancelled_orders
FROM
    sales AS sale
    INNER JOIN locations AS location ON location.location_id = sale.location_id
    INNER JOIN people    AS person   ON     person.person_id = sale.customer_id
GROUP BY
    dat,location

The results for new_customers is showing wrong, often more than total_customers. Where is it wrong, and how to correct this?

The results are like this:

    dat location    total_orders    sales   discounts   taxes   new_customers   total_customers cancelled_orders
15-03-14    Location1   52  1355    0   129.04  4   2   0
16-03-14    Location1   56  280 0   30  2   1   0
16-03-14    Location2   59  2518    0   212.2   3   6   2

As you might have guessed from the query, sales table has the columns sale_id,sale_time,sale_cost,sale_discount,sale_tax,sale_amount, is_cancelled (tinyint with values 0 or 1), and customer_id

People table has the columns person_id, first_name, added_on

By comparing the date(salessale_time) to date(person.added_on), I want to list customers added on that date

Upvotes: 1

Views: 919

Answers (1)

Santosh Achari
Santosh Achari

Reputation: 3006

I modified the query to the following to get new customers also in the result set. 

SELECT DATE(sale.sale_time) AS dat, location.location_name as location, (sale.sale_id) AS total_orders, 
SUM(CASE sale.is_cancelled WHEN 0 THEN sale.sale_amount ELSE 0 END) AS sales, 
SUM(CASE sale.is_cancelled WHEN 0 THEN sale.sale_discount ELSE 0 END) AS discounts,
SUM(CASE sale.is_cancelled WHEN 0 THEN sale.sale_tax ELSE 0 END) AS taxes, 
count(distinct(sale.customer_id)) AS total_customers, 
(select count(person_id) from people where date(added_on) = date(sale.sale_time) and person_id = sale.customer_id) as new_customers,
SUM(CASE sale.is_cancelled WHEN 1 THEN 1 ELSE 0 END) AS cancelled_orders
FROM sales AS sale
INNER JOIN locations AS location ON location.location_id = sale.location_id
GROUP BY dat,location;

Upvotes: 1

Related Questions