MySQL how to conditionally exclude records when grouping

Question

I need some help writing a query and I don't really know where to start. My data looks something like this (simplified for this post):

 mid            dbaname            profit   payment   takeback  bonus   custid  uid
 8788260180066  Baby's Room The    37.5     -10       27.5      13.75   1025    A6E5B9243B1445978F49961574912E8D
 8788014125552  Window Brite       0        0         0         0       1025    A6E5B9243B1445978F49961574912E8D
 8788014125552  Window Brite       0        0         0         0       1025    B89D1424AB2649E38386EDC287D5C862
 8788014123644  Mr. Wash It        10       -10       0         0       1025    A6E5B9243B1445978F49961574912E8D
 8788014123644  Mr. Wash It        10       -10       0         0       1025    B89D1424AB2649E38386EDC287D5C862

The 8th column has a userid in it, something like this: B89D1424AB2649E38386EDC287D5C862. What you can notice from this data is that each account/record has is duplicated having the admin's userid in the 8th column (the admin's userid is A6E5B9243B1445978F49961574912E8D). The first record only has one occurrence, because that account actually belongs to the admin while the others actually belong to the other userid (they just show up in the admin's view as well).

Ok, here is what I need:

Sum the 3rd column, excluding the admin's record when the account is actually owned by another user
Even though the admin account is excluded from the other sums it should be included when the account is owned by the admin himself

So, the results should be something like this:

 mid            dbaname            profit   payment   takeback  bonus   custid  uid
 8788260180066  Baby's Room The    37.5     -10       27.5      13.75   1025    A6E5B9243B1445978F49961574912E8D
 8788014125552  Window Brite       0        0         0         0       1025    B89D1424AB2649E38386EDC287D5C862
 8788014123644  Mr. Wash It        10       -10       0         0       1025    B89D1424AB2649E38386EDC287D5C862

Remember, I've somewhat simplified my problem and dataset to make it easier to understand, the full dataset will actually need the SUM() function on column 3 and the GROUP BY directive.

I don't see the point in sum/group by if there is always only one row per account and thus only one value in column 3 per account. Aggregation involves 2+ rows and you only seem to be interested in excluding rows and keeping 1 per account. It's not clear what you want to aggregate or why. — Brian DeMilia, Mar 26 '14 at 01:33
yea I'm sorry i didn't explain that part but essentially you hit the nail on the head. I do have more than one row per account, i just trimmed down the data to make my point... evidently i didn't do a great job — sadmicrowave, Mar 26 '14 at 01:39
@BrianDeMilia here is another post I have made with the full query that I am using currently, the problem is that it ALWAYS excludes the admin id, and I need it CONDITIONALLY excluded http://stackoverflow.com/questions/22262860/mysql-refactoring-lengthy-query — sadmicrowave, Mar 26 '14 at 01:46
If the record is owned by the admin, does that mean there is only 1 row per mid (like you examples) or does it mean every row of an admin owned account (that is, it has more than 1 row) will have the admin ID in the uid? — AgRizzo, Mar 26 '14 at 01:51
@AgRizzo there will only be 1 unique row for the admin in that case — sadmicrowave, Mar 26 '14 at 02:27

Brian DeMilia · Accepted Answer · 2014-03-26T01:52:17.520

Col1 refers to your first column name, col3 your 3rd column name, etc.

The first part of this query grabs the non-admin account for accounts having col8 admin and non-admin.

The second part of this query grabs the admin account for accounts having col8 just admin.

select col1, sum(col3)
  from tbl x
 where exists (select 1
          from tbl y
         where y.col1 = x.col1
           and y.col8 = 'B89D1424AB2649E38386EDC287D5C862')
   and exists (select 1
          from tbl y
         where y.col1 = x.col1
           and y.col8 <> 'B89D1424AB2649E38386EDC287D5C862')
   and y.col8 <> 'B89D1424AB2649E38386EDC287D5C862'
 group by col1
union all
select col1, sum(col3)
  from tbl x
 where not exists (select 1
          from tbl y
         where y.col1 = x.col1
           and y.col8 <> 'B89D1424AB2649E38386EDC287D5C862')
   and col8 = 'B89D1424AB2649E38386EDC287D5C862'
 group by col1

AgRizzo · Answer 2 · 2014-03-26T01:46:34.067

0

Choose all the ones owned by the admin and UNION to the non-admin ones

SELECT *
FROM foo
GROUP BY uid
HAVING COUNT(*)=1;
UNION
SELECT *
FROM foo
WHERE uid <> 'A6E5B9243B1445978F49961574912E8D';

edited Mar 26 '14 at 01:46

answered Mar 26 '14 at 01:26

AgRizzo

5,261
1
13
28

In this particular example I am posting a dataset from the `globalreport` table, however, this same theme exists throughout ALL the tables in that posted SO question, so if the solution is to use a UNION on each table, then that is going to get pretty lengthly as there are already tons of unions in that query – sadmicrowave Mar 26 '14 at 01:51
Its either UNIONs or subqueries. In either case, if it is complicated to read, then I would suggest you start using views to abstract the logic for readability and maintainability – AgRizzo Mar 26 '14 at 01:53

MySQL how to conditionally exclude records when grouping

2 Answers2