Solved

MYSQL Comparing Rows in the Same Table by Months

Posted on 2016-07-31
13
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 41

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 110

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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
LVL 110

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 110

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 110

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 41

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 41

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

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‚Ķ
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

691 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