Logic type question: grouping / adding columns in mySql

Question

SO.

I have a mySql table called 'houses'. The data I pull into houses comes in a format where there is a unique identifier (MLS) and each line item comes in with 'listoffice' and 'selloffice' as fields, too.

I can group and sum by either list office or selloffice but can I do a single group by / sum by using both fields.

E.g.: select count(mls) from house group by listoffice and selloffice order by count desc?

The intent is a list of sales that combines listoffice and selloffice where they are the same.

Edit: my intent is misstated. I am looking for an aggregrate list that combines the listoffice and selloffice count where they are the same. Where it counts the listoffice and selloffice instances, adds them, then produces a list of the ranked offices.

@forpas, that answers the original question as stated. Thank you! — Rob Thompson
– Rob Thompson, Commented Jul 13, 2019 at 18:57

Gordon Linoff · Accepted Answer · 2019-07-13 18:51:48Z

1

I think you want to unpivot the data and aggregate:

select office, sum(islist) as numlists, sum(issell) as numsells,
       sum(islist + issell) as numtotal
from ((select listoffice as office, 1 as islist, 0 as issell
       from house
      ) union all
      (select selloffice, 0, 1
       from house
      )
     ) sl
group by office
order by numtotal desc;

answered Jul 13, 2019 at 18:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rob Thompson Over a year ago

this is EXACTLY my intent. Thank you!

Rob Thompson Over a year ago

I've been experimenting with this for a bit today. Where would you put a conditional block in here? E.g., "where closed_date >= '2019-01-01'

Gordon Linoff Over a year ago

@RobThompson . . . Add it to each of the subqueries.

Collectives™ on Stack Overflow

Logic type question: grouping / adding columns in mySql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related