Solved

MYSQL Comparing Rows in the Same Table by Months

Posted on 2016-07-31
13
68 Views
Last Modified: 2016-08-04
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
Comment
Question by:mrroy69
  • 6
  • 4
  • 3
13 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41736924
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 41737492
Please clarify.  Where is the expected output for this line?

5675   | $150        | FEB

Thanks, ~Ray
0
 

Author Comment

by:mrroy69
ID: 41737743
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 41737900
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
 

Author Comment

by:mrroy69
ID: 41738172
I'm sorry that was a typo on my part they both should be 5676
1
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41741192
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
 

Author Comment

by:mrroy69
ID: 41741405
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 41741490
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
 

Author Closing Comment

by:mrroy69
ID: 41741556
Thank you!
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41741571
Agree with Ray. My query works fine for the information provided initially. Expected a split here but not worried about points.
0
 

Author Comment

by:mrroy69
ID: 41741579
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
 
LVL 40

Expert Comment

by:Sharath
ID: 41741608
Well. In that case, it should have communicated that it's not working.
1
 

Author Comment

by:mrroy69
ID: 41743286
I apologize, you are correct. Please forgive me.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question