Jump to content

Subtraction Question

tommahh

By tommahh
in MySQL Help

Recommended Posts

tommahh Newbie

tommahh

Posted
Posted
Hello,

I have been trying to figure out a way to subtract two numbers from each other (numbers created by using SUM), but can't seem to get it. I've tried looking in the mysql docs and each different search I do seems to land me at this page:

http://dev.mysql.com/doc/refman/4.1/en/arithmetic-functions.html

The user is inputting two dates ($end_date01 and $end_date02) to check inventory levels of a certain item on that date. They are also selecting $fb_type and $fb_color from a select form. I am able to pull both numbers in different queries, but where I am stuck is subtracting the first from the second. This is the code I am using to pull both of the starting numbers:

[color=red]
  $query01 = "SELECT fb_color, fb_type, SUM(yards) as stock01
            FROM  my_inv
WHERE (fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date01}\"
AND date_out > \"{$end_date01}\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date01}\"
AND date_out = \"0000-00-00\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date01}\"
AND date_out = \"{$end_date01}\"
AND in_transit < 1)
GROUP BY fb_type, fb_color";

  $query02 = "SELECT fb_color, fb_type, SUM(yards) as stock02
            FROM  my_inv
WHERE (fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date02}\"
AND date_out > \"{$end_date02}\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date02}\"
AND date_out = \"0000-00-00\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date02}\"
AND date_out = \"{$end_date02}\"
AND in_transit < 1)
GROUP BY fb_type, fb_color";
[/color]

When I output this, I get the name followed by a column showing stock1 and a column showing stock2. Ideally, I would like to then do "stock2 - stock1" and output that in the next column....which is where I am stuck.

Any suggestions?
Link to comment
https://forums.phpfreaks.com/topic/21620-subtraction-question/
Share on other sites
btherl Member

btherl

Posted
Posted
Hmm.. I think it could be done with a join.  But it could also be done in PHP.  For a join, it would look like this:

[code]$sql = "SELECT fb_color, fb_type, stock02 - stock01 as subbed
  FROM ( SELECT fb_color, fb_type, SUM(yards) as stock01 FROM my_inv WHERE ... )
  JOIN ( SELECT fb_color, fb_type, SUM(yards) as stock02 FROM my_inv WHERE ... )
  USING (fb_color, fb_type)"[/code]

Those subqueries there where I put "..." will be the entire queries you already have.  The idea is that before joining, you have output like

Output 1:
fb_color, fb_type, stock01
Output 2:
fb_color, fb_type, stock02

When you join on fb_color and fb_type, you get:
Joined output:
fb_color, fb_type, stock01, stock02

Then you just need to specify that you want (stock02 - stock01) as an output column.

You could simplify your initial queries like this:

[code]$query01 = "SELECT fb_color, fb_type, SUM(yards) as stock01
            FROM  my_inv
        WHERE fb_type = \"{$fb_type}\"
        AND fb_color = \"{$fb_color}\"
        AND date_in <= \"{$end_date01}\"
        AND in_transit < 1
        AND (date_out >= \"{$end_date01}\"
                OR date_out = \"0000-00-00\"
                )
        GROUP BY fb_type, fb_color";[/code]

That will work since only date_out varies between each OR branch.  You can also combine the "greater" and "equal" into a single condition.
Link to comment
https://forums.phpfreaks.com/topic/21620-subtraction-question/#findComment-96490
Share on other sites
tommahh Newbie

tommahh

Posted
  • Author
Posted
Thank you for your reply. I took a few days off from this problem as it was frustrating me greatly. I am a bit new to this, so I am kind of unsure what you meant by your possible solution. Below is the code for the entire page (with all my comments that help me understand just what is what), maybe it may help you to help me understand how I am supposed to use the information you supplied (or anybody else who would like to weigh in as well). Sorry for the inconvience, I'm a bit new to this : /

[color=red]

<? require "../db.inc"; ?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>test report</title>
<link rel="STYLESHEET" type="text/css" href="../_s/output.css">
</head>
<body>
<?
  // ... then print out a header
  print "<div class=\"header\"><strong>Report:</strong> Difference in Total yards of $fb_color $fb_type between $end_date01 and $end_date02</div>
\n <div class=\"main\">";

// show all information in a <table>
  function display_list01($connection, $query01, $end_date01)
  {
    // run the query
    if (!($result = @ mysql_query ($query01, $connection)))
        showerror();

    // find out how many rows are available
    $rowsFound = @ mysql_num_rows($result);

    // if the query has results...
    if ($rowsFound > 0)
    {
// and start a <table>
        print "\n<table>\n<tr>" .
  "\n\t<th>Fabric Type</th>" .
              "\n\t<th>Fabric Color</th>" .
              "\n\t<th>Starting Yards</th>" .
              "\n\t<th>Ending Yards</th>" .
  "\n\t<th>Difference</th>\n</tr>";



// fetch each of the query rows
        while ($row = @ mysql_fetch_array($result))
        {



// print one row of results (this is a while loop)
            print "\n<tr>\n\t\n\t<td>{$row["fb_type"]}&nbsp;</td>" .
              "\n\t<td>{$row["fb_color"]}&nbsp;</td>" .
              "\n\t<td>{$row["stock01"]}&nbsp;</td>\n";



        } // end while loop

    } // end if $rowsFound body

  } // end of function


// ########### this is what outputs the data ########### //

// connect to the db server
  if (!($connection = @ mysql_connect($hostname, $username, $password)))
    die("Could not connect");

// secure the user parameter $end_date
  $end_date01 = mysqlclean($_GET, "end_date01", 30, $connection);
  $fb_type = mysqlclean($_GET, "fb_type", 30, $connection);
  $fb_color = mysqlclean($_GET, "fb_color", 30, $connection);

  if (!mysql_select_db($databaseName, $connection))
    showerror();




// start a query ...
  $query01 = "SELECT fb_color, fb_type, SUM(yards) as stock01
            FROM  my_inv
WHERE (fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date01}\"
AND date_out > \"{$end_date01}\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date01}\"
AND date_out = \"0000-00-00\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date01}\"
AND date_out = \"{$end_date01}\"
AND in_transit < 1)
GROUP BY fb_type, fb_color";

// run the query and show the results
  display_list01($connection, $query01, $fb_color, $fb_type, $end_date01);
?>





<?


// query02 continuing in the new <table>
  function display_list02($connection, $query02, $end_date02)
  {
    // run the query
    if (!($result = @ mysql_query ($query02, $connection)))
        showerror();

    // find out how many rows are available
    $rowsFound = @ mysql_num_rows($result);

    // if the query has results...
    if ($rowsFound > 0)
    {


// fetch each of the query rows
        while ($row = @ mysql_fetch_array($result))
        {



// print one row of results (this is a while loop)
            print "\n\t<td>{$row["stock02"]}&nbsp;</td>\n" .
  "\n\t<td>bleh&nbsp;</td>\n</tr>";



        } // end while loop

        // finish the <table>
        print "\n</table>";
    } // end if $rowsFound body

    // bottom navigation stuff
    print "<br /><strong>Additional Navigation</strong>:<br />
<img src=\"../_m/icon_info.gif\" width=\"11\" height=\"11\" border=\"0\" />&nbsp;<a href=\"../index.php\">Back to Company Reports</a><br /><br />
<img src=\"../_m/icon_info.gif\" width=\"11\" height=\"11\" border=\"0\" />&nbsp;<a href=\"../index2.php\">Back to Customer Reports</a><br />";

  } // end of function


// ########### this is what outputs the data ########### //

// connect to the db server
  if (!($connection = @ mysql_connect($hostname, $username, $password)))
    die("Could not connect");

// secure the user parameter $end_date
  $end_date02 = mysqlclean($_GET, "end_date02", 30, $connection);
  $fb_type = mysqlclean($_GET, "fb_type", 30, $connection);
  $fb_color = mysqlclean($_GET, "fb_color", 30, $connection);

  if (!mysql_select_db($databaseName, $connection))
    showerror();

  $query02 = "SELECT fb_color, fb_type, SUM(yards) as stock02
            FROM  my_inv
WHERE (fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date02}\"
AND date_out > \"{$end_date02}\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date02}\"
AND date_out = \"0000-00-00\"
AND in_transit < 1)
OR
(fb_type = \"{$fb_type}\"
AND fb_color = \"{$fb_color}\"
AND date_in <= \"{$end_date02}\"
AND date_out = \"{$end_date02}\"
AND in_transit < 1)
GROUP BY fb_type, fb_color
ORDER BY fb_type, fb_color ASC";

// run the query and show the results
  display_list02($connection, $query02, $fb_color, $fb_type, $end_date02);
?>
</div>

</body>
</html>
[/color]


As shown, this prints the following:

fb_type | fb_color | stock01 | stock02 | bleh


Thanks for your help with this. I am trying to understand!

Link to comment
https://forums.phpfreaks.com/topic/21620-subtraction-question/#findComment-98760
Share on other sites
tommahh Newbie

tommahh

Posted
  • Author
Posted
Sure thing.

I am pulling two different sets of information (stock levels on one date and stock levels on another date). That part I have accomplished. The next part (where I am stuck) is subtracting one from the other (stock01 - stock02) and printing it to the screen to find the difference.
Link to comment
https://forums.phpfreaks.com/topic/21620-subtraction-question/#findComment-99317
Share on other sites
tommahh Newbie

tommahh

Posted
  • Author
Posted
Here is a dump from phpMyAdmin (it's what I use to create and edit)


[color=red]
--
-- Table structure for table `my_inv`
--

CREATE TABLE `my_inv` (
  `fb_id` int(11) NOT NULL auto_increment,
  `fb_type` varchar(100) collate latin1_bin NOT NULL default '',
  `fb_color` varchar(100) collate latin1_bin NOT NULL default '',
  `date_in` date NOT NULL default '0000-00-00',
  `date_out` date NOT NULL default '0000-00-00',
  `negotiable` varchar(100) collate latin1_bin NOT NULL default '',
  `carton_num` varchar(100) collate latin1_bin NOT NULL default '',
  `yards` decimal(9,1) NOT NULL default '0.0',
  `release_num` varchar(100) collate latin1_bin NOT NULL default '',
  `po_num` varchar(100) collate latin1_bin NOT NULL default '',
  `invoice_num` varchar(100) collate latin1_bin NOT NULL default '',
  `customer` varchar(100) collate latin1_bin NOT NULL default '',
  `warehouse` varchar(100) collate latin1_bin NOT NULL default '',
  `in_transit` tinyint(1) NOT NULL default '0',
  `comments` varchar(100) collate latin1_bin NOT NULL default '',
  PRIMARY KEY  (`fb_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=82005 ;
[/color]

The two items I would like subtracted are taken from these two queries:

This one is "as stock01"

[color=red]
$query01 = "SELECT fb_color, fb_type, SUM(yards) as stock01
            FROM  my_inv
        WHERE (fb_type = \"{$fb_type}\"
        AND fb_color = \"{$fb_color}\"
        AND date_in <= \"{$end_date01}\"
        AND date_out > \"{$end_date01}\"
        AND in_transit < 1)
        OR
        (fb_type = \"{$fb_type}\"
        AND fb_color = \"{$fb_color}\"
        AND date_in <= \"{$end_date01}\"
        AND date_out = \"0000-00-00\"
        AND in_transit < 1)
        OR
        (fb_type = \"{$fb_type}\"
        AND fb_color = \"{$fb_color}\"
        AND date_in <= \"{$end_date01}\"
        AND date_out = \"{$end_date01}\"
        AND in_transit < 1)
        GROUP BY fb_type, fb_color";
[/color]

This one is "as stock02"

[color=red]
$query02 = "SELECT fb_color, fb_type, SUM(yards) as stock02
            FROM  my_inv
        WHERE (fb_type = \"{$fb_type}\"
        AND fb_color = \"{$fb_color}\"
        AND date_in <= \"{$end_date02}\"
        AND date_out > \"{$end_date02}\"
        AND in_transit < 1)
        OR
        (fb_type = \"{$fb_type}\"
        AND fb_color = \"{$fb_color}\"
        AND date_in <= \"{$end_date02}\"
        AND date_out = \"0000-00-00\"
        AND in_transit < 1)
        OR
        (fb_type = \"{$fb_type}\"
        AND fb_color = \"{$fb_color}\"
        AND date_in <= \"{$end_date02}\"
        AND date_out = \"{$end_date02}\"
        AND in_transit < 1)
        GROUP BY fb_type, fb_color
        ORDER BY fb_type, fb_color ASC";
[/color]

They are basically the same query, just a different $end_date varible given by the user (it's the two dates they want to compare). I am able to display the stock levels on both $end_date01 (which is shown by stock01) and $end_date02 (which is shown by stock02). What I would like to do next is show the difference between them (which would be subtracting stock02 from stock01).

The code for the entire page (except for the db.inc which has the db login information in it) is in my pervious post so you can see how it is all strung together.

Thanks for looking into this for me.
Link to comment
https://forums.phpfreaks.com/topic/21620-subtraction-question/#findComment-99891
Share on other sites
tommahh Newbie

tommahh

Posted
  • Author
Posted
/bump

I've been reading up on JOIN trying to make sense of this and can't seem to grasp it. If somebody understands what is going on here, could they explain in terms that I might understand? It's probably easier than I am making it out to be, it's just not clicking at the moment.

Thanks for reading :)
Link to comment
https://forums.phpfreaks.com/topic/21620-subtraction-question/#findComment-103573
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.