CODEWITHSUNDEEP
sqljoin
DevG
DevG

Reputation: 494

Calculating sum of jobs processed every month using two tables

I have these two tables:

  1. TIME (this table contains the time_id which in turn gives the details like the day,month, year etc)

    |time_id|hour|day|month|year|
_____________________________
|1234   |1   |6  |9    |2013|
_____________________________
|1235   |2   |7  |9    |2013|
_____________________________
|1223   |2   |4  |8    |2014|
_____________________________
|1227   |2   |8  |8    |2014|

  2. SUM_JOBS_PROCESSED (this table contains the time_id and the no of jobs processed for this particular time_id.)

    |time_id|sum_of_jobs_processed|  
_______________________________
|1234   |5              |
_______________________________
|1235   |6              |
_______________________________
|1223   |4              |
_______________________________
|1227   |4              |

I am trying to write a query which should display something like this 

|month|year|sum_of_jobs_processed|
__________________________________
|9    |2013| 11                  |
__________________________________
|8    |2014| 8                   |
__________________________________

It should display total number of jobs processed for a month.

Could anyone please help me with these? I am able to find total number of jobs processed for a day, but number of jobs processed for a month, is not happening.

Upvotes: 0

Views: 44

Answers (2)

Prix
Prix

Reputation: 19528

Not sure I fully understood what you're trying, but I think this query should give you the desired result:

  SELECT t.month,
         t.year, 
         SUM(s.sum_of_jobs_processed)
    FROM bspm_dim_time t
    JOIN bspm_sum_jobs_day s
      ON t.time_id = s.time_id
GROUP BY t.month,
         t.year
ORDER BY t.year,
         t.month

Live DEMO.

Upvotes: 1

VISHMAY
VISHMAY

Reputation: 709

Try this:

    SELECT month,
           year,
           sum(sum_of_jobs_processed)
      FROM TIME
INNER JOIN SUM_JOBS_PROCESSED
        ON TIME.time_id = SUM_JOBS_PROCESSED.time_id
  GROUP BY month,
           year
  ORDER BY month,
           year

Mark as answer if correct.

Upvotes: 0

Related Questions