CODEWITHSUNDEEP
mysqlsum
Cito
Cito

Reputation: 1709

Wrong result using sum at MySQL

I have a view with a lot of data. I can got this results using subqueries (data that is ok and optimized):

+------------+
| attendance |
+------------+
|        319 |
|        102 |
|        598 |
|        113 |
|          6 |
|        279 |
|        366 |
|        146 |
|        669 |
|        205 |
|        123 |
+------------+

The next time some user update data, it shows this:

+------------+
| attendance |
+------------+
|        319 |
|        102 |
|        598 |
|        113 |
|          7 |
|        279 |
|        253 |
|        146 |
|        669 |
|        561 |
|        123 |
+------------+

Which is ok, 'cause the user that update the information was the one that before has 6 as attendance.

But the problem comes when I use that data as a temptable and then I make: 

 SELECT SUM(attendance) AS total FROM ( /* Subquery returning the above table */)

cause it returns (in the first place with one user having 6 as attendance):

+-------+
| total |
+-------+
|  3169 |
+-------+

And with 7:

+-------+
| total |
+-------+
|  3128 |
+-------+

When it should be 3170!!!

Ideas?

EDIT 1: Pasting the full query.

SELECT SUM(att_member) AS total
FROM
    (SELECT attendance AS att_member
     FROM
         (SELECT id_branch_channel, id_member, attendance, TIMESTAMP, id_event
          FROM view_event_attendance
          WHERE id_event = 782
          ORDER BY TIMESTAMP DESC) AS temptable
     GROUP BY 
              id_member) AS total_attendance_temp

EDIT 2: Pasting the query help I got from here

Select only last value using group by at mysql

Here is the schema of the view.

Upvotes: 1

Views: 5834

Answers (2)

Cito
Cito

Reputation: 1709

As @EdGibbs noticed, the subquery was returning bad results (something I had not noticed). So I read again my code and the answer to the other question I make reference and the things now work as they should, by doing this:

SELECT SUM(att_member) AS total
FROM
    (SELECT attendance AS att_member
     FROM
         (SELECT 
            id_branch_channel, 
            id_member, 
            substring(max(concat(from_unixtime(timestamp),attendance)) from 20) as attendance, 
            timestamp, 
            id_event
          FROM view_event_attendance
          WHERE id_event = 782
          GROUP BY id_event, id_member
          ORDER BY timestamp DESC) AS temptable
     ) AS total_attendance_temp

The only change I did was:

  • use: substring(max(concat(from_unixtime(timestamp),attendance)) from 20) as attendance instead of only attendace.
  • moving the group inside the first subquery.

So the problem was not in the SUM function at MySQL but in my query.

Thanks to all for taking the time to help me with this. Your suggestions help me to get the answer.

Upvotes: 0

Hazzit
Hazzit

Reputation: 6882

Lets disect the query, shall we?

I assume view_event_attendance has one record for every attendee (member) who attended at an event. id_event is a FK to that event, id_member is FK to the attendee. Your inner select gives you an ordered list of all members who attended event #782

SELECT id_branch_channel, id_member, attendance, TIMESTAMP, id_event
FROM view_event_attendance
WHERE id_event = 782
ORDER BY TIMESTAMP DESC

So far, so hoopy. Now you wrap this query in another one:

SELECT attendance AS att_member
FROM (subquery)
GROUP BY id_member

In most SQL dialetcs, this is simply a syntax error. MySQL allows this, but the result is probably not what you were looking for. You will get attendance column for every id_member who attended in said event. What you might actually expect is the SUM of attendances, but I you don't say so in your question. In any case, every selected field should either be in your GROUP BY clause or using an aggregate function, such as

SELECT SUM(attendance) AS att_member
FROM (subquery)
GROUP BY id_member

or

SELECT attendance AS att_member
FROM (subquery)
GROUP BY id_member, attendance

Having that said, I don't see a need for this to use a subquery to begin with. Let's assume you wanted to get the SUM as above, you could reprase this into a single SQL query:

SELECT SUM(attendance) AS att_member
FROM view_event_attendance
WHERE id_event = 782
GROUP BY id_member

If you then wanted the total, you could simply leave out the GROUP BY clause, leaving you with this:

SELECT SUM(attendance) AS att_member
FROM view_event_attendance
WHERE id_event = 782

If this doesn't work as expected, please describe in more detail what you're actually storing in view_event_attendance, and what you want the second query to calculate.

Upvotes: 1

Related Questions