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
mrroy69Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
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

1
Ray PaseurCommented:
Please clarify.  Where is the expected output for this line?

5675   | $150        | FEB

Thanks, ~Ray
0
mrroy69Author Commented:
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
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Ray PaseurCommented:
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?
0
mrroy69Author Commented:
I'm sorry that was a typo on my part they both should be 5676
1
Ray PaseurCommented:
In many ways, I like Sharath's query better, but I also feel like there is a backstory to this question that may contain important ideas about how to handle the query.  For example, no DBA would store a month name in textual values.  Instead, the date of payment would be stored in the form of an ISO-8601 datetime string.  The query would be able to extract the month, and we could use that month number for some kind of grouping and summation, either in the SQL statement or in the PHP script that processed the results set.

That said, this works, given what we have for test data and expected outputs.  Most of it is just setup - the moving parts start at line 123.
https://iconoun.com/demo/temp_mrroy69.php
<?php // demo/temp_mrroy69.php
/**
 * https://www.experts-exchange.com/questions/28960643/MYSQL-Comparing-Rows-in-the-Same-Table-by-Months.html
 *
 */
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// CREATE AN ARRAY TO USE FOR TEST DATA
$test_arrays = array
( [ "cnm" => "1234" , "amt" =>  "50", "mon" => "Jan" ]
, [ "cnm" => "1234" , "amt" =>  "75", "mon" => "Feb" ]

, [ "cnm" => "5676" , "amt" => "100", "mon" => "Jan" ]
, [ "cnm" => "5676" , "amt" => "150", "mon" => "Feb" ]

, [ "cnm" => "6754" , "amt" =>  "25", "mon" => "Jan" ]
, [ "cnm" => "6754" , "amt" =>  "50", "mon" => "Feb" ]

, [ "cnm" => "6787" , "amt" =>  "30", "mon" => "Jan" ]
, [ "cnm" => "6787" , "amt" =>   "0", "mon" => "Feb" ]
)
;



// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id  INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
, cnm VARCHAR(24)  NOT NULL DEFAULT ''
, amt DECIMAL(5,0) NOT NULL DEFAULT '0.0'
, mon VARCHAR(24)  NOT NULL DEFAULT ''
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_arrays as $arr)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_cnm  = $mysqli->real_escape_string($arr['cnm']);
    $safe_amt  = $mysqli->real_escape_string($arr['amt']);
    $safe_mon  = $mysqli->real_escape_string($arr['mon']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( cnm, amt, mon ) VALUES ( '$safe_cnm', '$safe_amt', '$safe_mon' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // ACTIVATE THIS TO GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    // $id  = $mysqli->insert_id;
    // echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_cnm $safe_amt $safe_mon</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT THE QUERY TO MAKE JANUARY VALUES COLLATE OUT BEFORE FEBRUARY
$sql = "SELECT cnm, amt, mon FROM my_table ORDER BY cnm ASC, mon DESC ";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// START THE TABLE OF OUTPUT
$out = '<table>' . PHP_EOL;
$out .= '<tr>';
$out .= '<th>C#</th>';
$out .= '<th>JAN</th>';
$out .= '<th>FEB</th>';
$out .= '</tr>' . PHP_EOL;

// SET THE ACCUMULATORS TO ZERO
$old = '?';
$t01 = 0;
$t02 = 0;

// FETCH THE DATA AND MAKE THE SUMMATIONS
while ($row = $res->fetch_object())
{
    if ($row->mon == 'Jan') $t01 += $row->amt;
    if ($row->mon == 'Feb') $t02 += $row->amt;
    if ($row->cnm != $old)
    {
        $out .= '<tr>';
        $out .= '<td>' . $row->cnm . '</td><td>' . '$' . $row->amt . '</td>';
        $old = $row->cnm;
    }
    else
    {
        $out .= '<td>' . '$' . $row->amt . '</td></tr>' . PHP_EOL;
    }
}
$out .= '<tr>' . '<td>TOTAL</td>' . "<td>\$$t01</td>" . "<td>\$$t02</td>" . '</tr>' . PHP_EOL;
$out .= '</table>' . PHP_EOL;
echo $out . PHP_EOL;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrroy69Author Commented:
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
0
Ray PaseurCommented:
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!
0
mrroy69Author Commented:
Thank you!
0
SharathData EngineerCommented:
Agree with Ray. My query works fine for the information provided initially. Expected a split here but not worried about points.
0
mrroy69Author Commented:
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
0
SharathData EngineerCommented:
Well. In that case, it should have communicated that it's not working.
1
mrroy69Author Commented:
I apologize, you are correct. Please forgive me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.