group by does not work correctly join two tables

Question

I want to get the number of tickets sold per payment method for each event and I've the follow query:

SELECT  count(distinct(a.performance_id)) as EventQuantity,
        sum(a.admission_count)  as TicketQuantity
FROM   vw_PrecioTipoZona_Paid as a 
WHERE 1 = 1
AND a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
GROUP BY a.performance_id

and I get this result, which is OK:

EventQuantity   TicketQuantity
   1                    203

But when join the table with other, the result which is sum, in this case a.admission_count is multiplied by the number of records in the other table.

The query who has problem is this:

SELECT      a.performance_id,
            count(distinct(a.performance_id)) as EventQuantity,
            sum(a.admission_count)  as TicketQuantity,
            b.payment_method as PaymentMethod
FROM   vw_PrecioTipoZona_Paid as a inner join vw_Payment_UserByPerformance as b
       on a.performance_id = b.performance_id
WHERE 
    1 = 1
    and a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
    group by a.performance_id, b.payment_method

With this query I'm getting this result:

EventQuantity   TicketQuantity  PaymentMethod
   1               10353            Cash
   1               5887             Card
   1               1624         MasterCardECommerce
   1                812           VisaEcommece

And this result is wrong, the result should be:

EventQuantity   TicketQuantity  PaymentMethod
       1             111             Cash
       1              63             Card
       1              17       MasterCardECommerce
       1              8             VisaEcommece

The vw_Payment_UserByPerformance view structure is the follow:

performance_id  user_role_id    userrole_name   userrole_group  date_transaction    user_id user_name   owner_user_id   owner_user_name amount_adm_net  amount_req_net  amount_charge_charge    amount_total    amount_net  chargeTransaction   tax payment_method

And the vw_PrecioTipoZona_Paid view structure is the follow:

performance_id  performance_name    performance_start_date  date_transaction    user_role_id    userrole_name   userrole_group  user_id user_name   price_type  price_zone  price_zone_priority admission_count NET charge1 charge2 charge3 charge4 charge5 GROSS

Do I have to make subquery? Where is the problem here?

Answer 1

MySQl allows you to incorrectly use group by. You should never use the technique you used in this query.

SELECT      a.performance_id,
            count(distinct(a.performance_id)) as EventQuantity,
            sum(a.admission_count)  as TicketQuantity,
            b.payment_method as PaymentMethod
FROM   vw_PrecioTipoZona_Paid as a inner join vw_Payment_UserByPerformance as b
       on a.performance_id = b.performance_id
WHERE 
    a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
    group by a.performance_id, b.payment_method

When you use group by the only way to guarantee correct results is to group by all the non-aggregated fields. All other databases make this part of the syntax and thus do not have this problem.

If this still does not give the correct results, then there is a problem with the specifics of what you intended vice what you wrote. We would need to see the business requirement, that table structure, the sample data in the tables and teh sample results in order to help you find the correct query.

Looking at your additional details added while I was writing this, I think you need to use a derived table.

SELECT      a.performance_id,
            count(a.performance_id) as EventQuantity,
            a.admission_count  as TicketQuantity,
            b.payment_method as PaymentMethod
FROM   (select performance_id, sum(admission_count) as Admissioncount vw_PrecioTipoZona_Paid 
WHERE a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
group by performance_id )as a
 inner join vw_Payment_UserByPerformance as b
       on a.performance_id = b.performance_id
    group by a.performance_id, b.payment_method