0

i have tried to find top retailer and top distributor with below query.

SELECT
  (SELECT SUM(amount) AS total_amount
   FROM transactions tm
     LEFT JOIN user_master um ON um.p_id = tm.user_id
   WHERE um.user_type = 'RT'
   GROUP BY ma_user_id
   ORDER BY total_amount DESC
   LIMIT 1) AS rt_topest,
  (SELECT SUM(amount) AS total_amount
   FROM transactions tm
     LEFT JOIN user_master um ON um.p_id = tm.user_id
   WHERE um.user_type = 'DT'
   GROUP BY ma_user_id
   ORDER BY total_amount DESC
   LIMIT 1) AS dt_topest
FROM transactions tm
WHERE tm.addedon >= DATE_SUB(curdate(), INTERVAL DAYOFMONTH(now()) - 1 DAY) AND
      tm.addedon <= now()
LIMIT 1;

table structure

CREATE TABLE user_master
(
  user_master_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  p_id INT UNSIGNED NOT NULL,
  firstname VARCHAR(50),
  lastname VARCHAR(50),
  user_type CHAR(3) DEFAULT 'RT',
  addedon TIMESTAMP DEFAULT current_timestamp(),
);



CREATE TABLE transactions
(
  transaction_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  user_id INT,
  amount DECIMAL(12,4),
  addedon TIMESTAMP DEFAULT current_timestamp(),
  updatedon TIMESTAMP DEFAULT current_timestamp(),
);

how to find Top paid user in last month from below data as single row.

+----------------+---------+----------+---------------------+
| transaction_id | user_id | amount   | addedon             |
+----------------+---------+----------+---------------------+
|              1 |   18923 | 100.0000 | 2022-01-19 15:01:11 |
|              2 |   28766 | 100.0000 | 2022-01-18 18:01:11 |
|              3 |   28629 | 100.0000 | 2022-01-18 18:01:11 |
|              4 |   28699 | 100.0000 | 2022-01-18 18:01:11 |
|              5 |   28699 | 100.0000 | 2022-01-14 18:01:11 |
|              6 |   28699 | 100.0000 | 2021-12-10 18:01:11 |
|              7 |  129138 | 100.0000 | 2022-01-14 18:01:11 |
|              8 |   28627 | 100.0000 | 2022-01-17 18:01:11 |
|              9 |   29150 | 100.0000 | 2022-01-13 18:01:11 |
|             10 |   28619 | 100.0000 | 2021-12-31 18:01:11 |
|             12 |   28629 | 100.0000 | 2021-12-19 15:01:11 |
+----------------+---------+----------+---------------------+

expected result

+-----------+-----------+
| rt_topest | dt_topest |
+-----------+-----------+
|  300.0000 |  100.0000 |
+-----------+-----------+

Sample Data

transactions

+----------------+---------+----------+---------------------+
| transaction_id | user_id | amount   | addedon             |
+----------------+---------+----------+---------------------+
|              1 |   18923 | 100.0000 | 2022-01-19 15:01:11 |
|              2 |   28766 | 100.0000 | 2022-01-18 18:01:11 |
|              3 |   28629 | 100.0000 | 2022-01-18 18:01:11 |
|              4 |   28699 | 100.0000 | 2022-01-18 18:01:11 |
|              5 |   28699 | 100.0000 | 2022-01-14 18:01:11 |
|              6 |   28699 | 100.0000 | 2021-12-10 18:01:11 |
|              7 |  129138 | 100.0000 | 2022-01-14 18:01:11 |
|              8 |   28627 | 100.0000 | 2022-01-17 18:01:11 |
|              9 |   29150 | 100.0000 | 2022-01-13 18:01:11 |
|             10 |   28619 | 100.0000 | 2021-12-31 18:01:11 |
|             12 |   28629 | 100.0000 | 2021-12-19 15:01:11 |
+----------------+---------+----------+---------------------+

Sample Data users

+----+-------+----------+-----------+
| id | p_id  | name     | user_type |
+----+-------+----------+-----------+
|  3 | 18923 | merchan1 | DT        |
|  4 | 28766 | merchan2 | RT        |
|  9 | 28699 | Kirti    | RT        |
| 19 | 28629 | test1    | DT        |
| 63 | 28627 | test2    | RT        |
+----+-------+----------+-----------+
2
  • Welcome to the DBA.SE community. What is your actual question? Where is the problem? Do you have sample data? Please edit your question and add as many details as possible. Please also consider reading How to Ask. Thanks.
    – John K. N.
    Commented Jan 21, 2022 at 15:11
  • See the tag I added.
    – Rick James
    Commented Jan 21, 2022 at 20:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.