I have this table:

and I want this:

This is a table with 7 column and rows equal to the number of methods.

• AMMOUNT: sum of ammount in one month of one method
• N_TRANSACTIONS: number of transaction in one month with one method
• N_CUSTOMER: number of customers (id) who used that method in one month

Can I get it with just one query?

CodePudding user response：

You want to aggregate your data by method and have separate columns for January data and February data. You get this with conditional aggregation (CASE expression inside the aggregate function),

select
  method,
  sum(case when month(date) = 1 then ammount else 0 end) as ammount_sum_jan,
  count(case when month(date) = 1 then 1 end) as n_transaction_jan,
  count(distinct case when month(date) = 1 then id end) as n_customer_jan,
  sum(case when month(date) = 2 then ammount else 0 end) as ammount_sum_feb,
  count(case when month(date) = 2 then 1 end) as n_transaction_feb,
  count(distinct case when month(date) = 2 then id end) as n_customer_feb
from mytable
group by method
order by method;

CodePudding user response：

It is called pivot, and would for dfix dates look like this. An aggregation of the methid for the year and month, and you can COUNT or SUM your number

select 
  `method`,
  SUM(CASE WHEN EXTRACT( YEAR_MONTH FROM `date` ) = '202201' then `ammount` ELSe 0 END) ammount_sum_jan,
  SUM(CASE WHEN EXTRACT( YEAR_MONTH FROM `date` ) = '202201' then 1 ELSe 0 END) n_transaction_jan,
  COUNT(DISTINCT CASE WHEN EXTRACT( YEAR_MONTH FROM `date` ) = '202201' then `d` ELSe 0 END)    n_customer_jan,
  SUM(CASE WHEN EXTRACT( YEAR_MONTH FROM `date` ) = '202202' then `ammount` ELSe 0 END) ammount_sum_feb,
  SUM(CASE WHEN EXTRACT( YEAR_MONTH FROM `date` ) = '202202' then 1 ELSe 0 END) n_transaction_feb,
  COUNT(DISTINCT CASE WHEN EXTRACT( YEAR_MONTH FROM `date` ) = '202202' then `d` ELSe 0 END)    n_customer_feb
from tab1
GROUP BY `method`

http://www.sqlfiddle.com/#!9/31d8ef/10

much more interesting would be to ake that dynamic