CODEWITHSUNDEEP
mysqlsqlgroup-by
Skeeve
Skeeve

Reputation: 1255

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

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!

Upvotes: 2

Views: 371

Answers (3)

DerekCate
DerekCate

Reputation: 306

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

Upvotes: 2

StevieG
StevieG

Reputation: 8729

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

Upvotes: 1

PWilliams0530
PWilliams0530

Reputation: 170

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

Upvotes: 1

Related Questions