CODEWITHSUNDEEP
mysqlsqlgroup-bysum
sadmicrowave
sadmicrowave

Reputation: 40892

MySQL how to conditionally exclude records when grouping

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:

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.

Upvotes: 0

Views: 1113

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

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

Upvotes: 2

AgRizzo
AgRizzo

Reputation: 5271

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';

Upvotes: 0

Related Questions