mrroy69

asked on 7/31/2016

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

MySQL ServerPHP

Last Comment

mrroy69

8/22/2022 - Mon

Sharath S

7/31/2016

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

Ray Paseur

8/1/2016

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

5675 | $150 | FEB

Thanks, ~Ray

mrroy69

8/1/2016

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Ray Paseur

8/1/2016

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?

mrroy69

8/1/2016

ASKER

I'm sorry that was a typo on my part they both should be 5676

ASKER CERTIFIED SOLUTION

Ray Paseur

8/3/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

mrroy69

8/3/2016

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Ray Paseur

8/3/2016

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!

mrroy69

8/3/2016

ASKER

Thank you!

Sharath S

8/3/2016

Agree with Ray. My query works fine for the information provided initially. Expected a split here but not worried about points.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

mrroy69

8/3/2016

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

Sharath S

8/3/2016

Well. In that case, it should have communicated that it's not working.

mrroy69

8/4/2016

ASKER

I apologize, you are correct. Please forgive me.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.