MySQL get COUNT and SUM on different GROUP BY in one SELECT

Question

I need to select data from the table that looks somewhere about this:

monthStart  monthEnd newPhones totalPhones oblid
1           1        1         2           1 
2           2        1         2           2
1           2        2         2           3
2           2        1         1           4
2           3        0         3           5

So I want to select 4 fields: month, count obj for months on the base of monthStart, sum of newPhones on the base of monthEnd, sum of totalPhones on the base of monthEnd.

So for this data I need to select this:

month  count  totalPhones newPhones
1      3      2           1
2      2      5           4
3      0      3           0

count for 1st month = 3 as we have 3 rows with monthStart = 1, but we have only one row with monthEnd = 1, so totalPhones for 1 month = 2, newPhones = 1 count for 3d month = 0 as we have 0 rows with monthStart = 3, but we have 3 totalPhones and 0 newPhones for monthEnd = 3 - we should show this data.

I've stuck with this. I've tried to select from the result of this select:

SELECT
monthStart,
monthEnd,
count(1) as uploaded,
sum(newPhones) as newPhones,
sum(totalPhones) as totalPhones
from TestGB
group by monthEnd, monthStart

but I can't get ideal result. Thank you for your help!

DerekCate · Accepted Answer · 2014-07-31 15:15:22Z

2

So if there is enough data that all months are represented, I think StevieG's answer works. With the smaller data sets like the sample data given, where month 3 is in the monthEnd but not in the monthStart, then there is a problem. Then you need something to make sure all months are represented, which I did with c, the coalesce's are just to make things pretty.

SELECT 
  c.month,
  coalesce(a.mycount,0),
  coalesce(b.totalPhones,0),
  coalesce(b.newphones,0)
FROM
  (SELECT monthStart as month FROM TestGB
   UNION
   SELECT monthEnd as month FROM TestGB) c 
LEFT OUTER JOIN 
  (SELECT
   monthStart as month,
   count(distinct obild) as mycount,
   from TestGB
   group by monthStart) a on a.month = c.month
LEFT OUTER JOIN 
  (SELECT
   monthStart as month,
   sum(newPhones) as newPhones,
   sum(totalPhones) as totalPhones
   from TestGB
   group by monthEnd) b ON b.month = c.month

answered Jul 31, 2014 at 15:15

DerekCate

3061 silver badge5 bronze badges

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

2 Comments

Skeeve Over a year ago

works good! only I had to change monthStart as month to monthEnd as month because that numbers relates to monthEnd. so this works very good! Could you tell me though - can I do this without subselects and unions or not?

DerekCate Over a year ago

Ah, yes, thanks for catching that. Unfortunately, I don't think you can do it without the subselects and unions. Because you are grouping by 2 different columns, you have to do those in different (sub)queries, and the subquery with the union is just because MySQL doesn't do full outer joins, otherwise we could do a full outer join between a and b and skip c.

PWilliams0530 · Accepted Answer · 2014-07-31 14:41:08Z

1

Maybe something like this....

CREATE TABLE #monthstartdata
(
    themonth INT,
    themonthcount int
)

CREATE TABLE #monthenddata
(
    themonth INT,
    totalphones INT,
    newphones INT,
)

INSERT INTO #monthstartdata
            ( themonth, themonthcount )
SELECT monthStart, COUNT(#monthstart) FROM TestGB
GROUP BY monthStart

INSERT INTO #monthenddata
        ( themonth, totalphones, newphones )
SELECT monthEnd, COUNT(totalphones), COUNT(newPhones) FROM TestGB
GROUP BY monthEnd

SELECT #monthstartdata.themonth, themonthcount, totalphones, newphones FROM #monthstartdata
INNER JOIN #monthenddata ON #monthstartdata.themonth = #monthenddata.themonth

answered Jul 31, 2014 at 14:41

PWilliams0530

1701 silver badge13 bronze badges

Comments

StevieG · Accepted Answer · 2014-07-31 14:42:35Z

1

Assuming I've understood the question properly, I suspect you want to do this with sub-queries.. Something like this:

SELECT 
  IFNULL(a.month,b.month)
  a.mycount,
  b.totalPhones,
  b.newphones
FROM
  (SELECT
   monthStart as month,
   count(distinct obild) as mycount,
   from TestGB
   group by monthStart) a
LEFT OUTER JOIN 
  (SELECT
   monthStart as month,
   sum(newPhones) as newPhones,
   sum(totalPhones) as totalPhones
   from TestGB
   group by monthEnd) b ON a.month = b.month

answered Jul 31, 2014 at 14:42

StevieG

8,7391 gold badge26 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL get COUNT and SUM on different GROUP BY in one SELECT

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related