CODEWITHSUNDEEP
mysql
AbsoluteBeginner
AbsoluteBeginner

Reputation: 505

Count number of orders, paymentRatio, return rate and date of last order per customer with MySQL

I have a table with the following columns:

|customerNum|OrderNumber|ArticleNumber|PaymentType|Returned?|OrderDate |
|1          |1          |1            |cash       |Yes      |01.01.2016|
|2          |2          |3            |credit     |No       |30.02.2016|
|1          |1          |2            |cash       |No       |01.01.2016|
|2          |2          |4            |credit     |Yes      |30.02.2016|
|3          |3          |2            |credit     |No       |17.05.2016|
|2          |4          |8            |cash       |Yes      |19.08.2016|

Expected Outcome:

customerNum|Numb.ofOrdersTot|ratioCash/CreditPayment|returnRate|LastOrder |
|1         |1               |1                      |0,5       |01.01.2016|
|2         |2               |0.5                    |0,66      |19.08.2016|
|3         |1               |0                      |0         |17.05.2016|

I want to count the total number of orders (not ordered articles) per customer (one Order can have more than one article), the ratio of cash payment and credit payment (number of cash payments / number of credit payments (one order with more than one article has the same payment method)) the return rate of customer (numberOfReturnedArticles/numberOfArticlesOrderedbycustomerTotal) and last order date

I tried already a part of it already but this is not working and not complete: 

`COUNT(*) AS Numb.ofOrdersTot,(COUNT(orderNumber)/COUNT(Returned? = Yes)) FROM table 1 group by customerNum;`

PLZ help me: it´s very important :/

Upvotes: 1

Views: 61

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Here is my Answer. please check it and look if the fields have the right type. Also i have add 2 second field 'orderdate2' with a other format. you can remove one. so your 'Expected Outcome' cant be from your input

SELECT
    customerNum AS 'customerNum'
    , SUM(1) AS 'Numb.ofOrdersTot'
    , SUM(IF(`PaymentType` = 'credit',1,0)) / SUM(1) AS 'ratioCash/CreditPayment'
    , SUM(IF(`Returned?` = 'Yes',1,0)) / SUM(1) AS 'returnRate'
    ,MAX(`OrderDate`) AS 'LastOrder'
    ,DATE_FORMAT(MAX(`OrderDate`),'%d.%m.%Y') AS 'LastOrder2'   
FROM orders
GROUP BY customerNum;

sample

mysql> SELECT * FROM orders;
+----+-------------+-------------+---------------+-------------+-----------+------------+
| id | customerNum | OrderNumber | ArticleNumber | PaymentType | Returned? | OrderDate  |
+----+-------------+-------------+---------------+-------------+-----------+------------+
|  1 |           1 |           1 |             1 | cash        | Yes       | 2016-01-01 |
|  2 |           2 |           2 |             3 | credit      | No        | 2016-02-29 |
|  3 |           1 |           1 |             2 | cash        | No        | 2016-01-01 |
|  4 |           2 |           2 |             4 | credit      | Yes       | 2016-02-29 |
|  5 |           3 |           3 |             2 | credit      | No        | 2016-05-17 |
|  6 |           2 |           4 |             8 | cash        | Yes       | 2016-08-19 |
+----+-------------+-------------+---------------+-------------+-----------+------------+
6 rows in set (0,00 sec)

mysql> SELECT
    ->     customerNum AS 'customerNum'
    ->     , SUM(1) AS 'Numb.ofOrdersTot'
    ->     , SUM(IF(`PaymentType` = 'credit',1,0)) / SUM(1) AS 'ratioCash/CreditPayment'
    ->     , SUM(IF(`Returned?` = 'Yes',1,0)) / SUM(1) AS 'returnRate'
    ->     ,MAX(`OrderDate`) AS 'LastOrder'
    ->     ,DATE_FORMAT(MAX(`OrderDate`),'%d.%m.%Y') AS 'LastOrder2'
    -> FROM orders
    -> GROUP BY customerNum;
+-------------+------------------+-------------------------+------------+------------+------------+
| customerNum | Numb.ofOrdersTot | ratioCash/CreditPayment | returnRate | LastOrder  | LastOrder2 |
+-------------+------------------+-------------------------+------------+------------+------------+
|           1 |                2 |                  0.0000 |     0.5000 | 2016-01-01 | 01.01.2016 |
|           2 |                3 |                  0.6667 |     0.6667 | 2016-08-19 | 19.08.2016 |
|           3 |                1 |                  1.0000 |     0.0000 | 2016-05-17 | 17.05.2016 |
+-------------+------------------+-------------------------+------------+------------+------------+
3 rows in set (0,00 sec)

mysql>

Upvotes: 1

Related Questions