CODEWITHSUNDEEP
mysqlsql
Rob Thompson
Rob Thompson

Reputation: 75

Logic type question: grouping / adding columns in mySql

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.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

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;

Upvotes: 1

Related Questions