• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

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
0
mrroy69
Asked:
mrroy69
  • 6
  • 4
  • 3
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now