Jump to content

Archived

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

tommahh

Subtraction Question

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
Having not really followed this thread to date, I can't jump to an explanation of the above... could you explain again what you're trying to accomplish in simple terms?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
OK... could you post the table structure, and the columns you want subtracted?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
OK... what progress did you make with btherl's suggestions?

Share this post


Link to post
Share on other sites
I tried them and didn't get too far. Either error msgs or no output at all was the result. As I am fairly new to this, I think it was more my not understanding then his solution not being accurate.

Share this post


Link to post
Share on other sites
/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 :)

Share this post


Link to post
Share on other sites
I'll see if I can find some time to play around with this.

Share this post


Link to post
Share on other sites

×

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.