Link to home
Create AccountLog in
Avatar of mrroy69
mrroy69

asked on

MYSQL Comparing Rows in the Same Table by Months

I have a table with different contract numbers and payments on each contract number for each month for example:

C#       | amount  | month
1234   |  $50         | JAN
1234   | $75          | FEB
5676   | $100        | JAN
5675   | $150        | FEB
6754   | $25          | JAN
6754   | $50          | FEB
6787   | $30          | JAN
6787   | $0            | FEB

I want to be able to do a comparison of each  contract number in a table comparing what was pain in JAN vs. what was paid in FEB so that result would be like

C#        | JAN     | FEB
1234    |  $50    | $75
5676    | $100   | $150
6754    | $25     | $50
6787    | $30     | $0
---------------------------
TOTAL | $205   | $225
Avatar of Sharath S
Sharath S
Flag of United States of America image

SELECT C#,
              MAX(CASE WHEN month = 'JAN' THEN amount END) AS JAN,
              MAX(CASE WHEN month = 'FEB' THEN amount END) AS FEB,
  FROM your_table
 GROUP BY C#

Open in new window

Please clarify.  Where is the expected output for this line?

5675   | $150        | FEB

Thanks, ~Ray
Avatar of mrroy69
mrroy69

ASKER

Greetings Ray the second table is the expected output, so as you see the February value  C# 5676

C#        | JAN     | FEB
5676    | $100   | $150

Sharath, I will try your solution and let you know. Thanks
OK, I understood that part in the output.  There are two different C# values in the input: 5676 and 5675.  I am guessing that this is a typo, and not intended to be part of the problem?
Avatar of mrroy69

ASKER

I'm sorry that was a typo on my part they both should be 5676
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mrroy69

ASKER

Greetings Ray, thanks for the response. There really isn't a backstory the months are stored as YYYY-MM
so for example 2016-01 = JAN, 2016-02 = FEB.

I will try both solutions and let you know which one works best
Sigh.  So the months are not stored in the way you showed us in the question?  For future reference, please show us the actual information.  It will save everyone a lot of time!
Avatar of mrroy69

ASKER

Thank you!
Agree with Ray. My query works fine for the information provided initially. Expected a split here but not worried about points.
Avatar of mrroy69

ASKER

Actually Sharath I tried your solution however, it did not give the expected results. Though it returned all of the rows most of the amounts showed up as 0.00.  I appreciate your help
Well. In that case, it should have communicated that it's not working.
Avatar of mrroy69

ASKER

I apologize, you are correct. Please forgive me.