1

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

Santosh Achari
  • 2,936
  • 7
  • 30
  • 52
  • What values are you expecting? Please elaborate. – nmenego Apr 07 '14 at 02:42
  • Please post schema and sample data. – Pankaj Gadge Apr 07 '14 at 02:42
  • As an initial assessment, I think that you're probably getting every sale.sale_time that matches the date that a person was added, so it would probably be far greater than total customers, yes. – William M-B Apr 07 '14 at 02:46
  • I added the result @nmenego. – Santosh Achari Apr 07 '14 at 02:52
  • I'm guessing that you're looking for something (perhaps) more like date(person.added_on) = date(min(sale.sale_time)) to get the first sale_time that a sale was made, but I don't know what flavor of SQL you're using. – William M-B Apr 07 '14 at 02:52
  • @william-m-b agreed. The other possibility is that it's not taking _distinct_ customer_ids .But how can I limit to persons in sale in the query? It's mysql. – Santosh Achari Apr 07 '14 at 02:53
  • 1
    You can use the ROW_NUMBER function if necessary and partition your query to sort the sale table by customer, then date, then join customer to person as the key. Oops, MYSQL doesn't have ROW_NUMBER, look here: http://stackoverflow.com/questions/11963818/row-number-equivalent-in-mysql-for-inserting. – William M-B Apr 07 '14 at 02:57
  • Partitioning the query makes sense. I think I got the solution, putting it up. Thanks again for the help. – Santosh Achari Apr 07 '14 at 03:09

1 Answers1

1

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;
Santosh Achari
  • 2,936
  • 7
  • 30
  • 52