Question 182489 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Group Report With Header and Detail?

I have table transactions with below details

id dt_trans category description amount
1 2022-08-05 Expense Food 60.00
2 2022-08-05 Travel Petrol 20.00
2 2022-08-08 Travel Petrol 20.00

then I want to create report based on below report

dt_trans category description amount
Expense
1 2022-08-05 Expense Food 60.00
Total 60.00
Travel
1 2022-08-05 Travel Petrol 20.00
2 2022-08-08 Travel Petrol 20.00
Total 40.00

Usually I will create a temporary table based on the report and add the row based on 2 queries.
Here’s the logic

Loop // query1 retrieve all the category

   query2 retrieve all the details based on category

end of loop query1

Is there a more simple way to create this report?

query:

DROP TABLE IF EXISTS `transaction`;
CREATE TABLE `transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cashaccount_id` int(11) DEFAULT NULL,
  `dt_trans` date DEFAULT NULL,
  `category` varchar(200) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `type` tinyint(4) DEFAULT NULL COMMENT '0 income 1 expense',
  `amount` float(10,2) DEFAULT 0.00,
  `dt_created` datetime DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `transaction` (`id`, `cashaccount_id`, `dt_trans`, `category`, `description`, `type`, `amount`, `dt_created`, `uid`) VALUES
(115, 1, '2021-10-10', 'Expense', 'Food', 1, 60.00, '2021-10-10 17:06:36', 1),
(121, 1, '2021-10-15', 'Travel', 'Petrol', 1, 20.00, '2021-10-15 01:01:15', 1),
(123, 1, '2021-10-05', 'Travel', 'Petrol', 0, 20.00, '2021-10-15 09:11:28', 1)
Tags:

2

Answers


  1. 0

    You may try to loop on programming side but here is a simple mysql query

    SELECT 
    s.*
FROM
    transaction s
        CROSS JOIN
    (SELECT 
        category
    FROM
        transaction) AS t
        GROUP BY s.id
    Login or Signup to reply.
  2. 0

    Try to union three select statements with a specific order for each one as the following:

    with t as
(
  select id, dt_trans, category, description, amount,
         dense_rank() over (order by category) as ord1, 
         2 as ord2
  from transaction
  
  union all
  
  select distinct category, '', '', '', '',
         dense_rank() over (order by category) as ord1,
         1 as ord2
  from transaction
  
  union all
  
  select  '', '', '', 'Total', 
          sum(amount),
          dense_rank() over (order by category) as ord1, 
          3 as ord2 
  from transaction
  group by category
)
select id, dt_trans, category, description, amount
from t
order by ord1, ord2

    But it would be better to perform this kind of formatting reports outside the SQL server.

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search