Solved

MYSQL Comparing Rows in the Same Table by Months

Posted on 2016-07-31
13
78 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

749 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