Jump to content


Photo

Subtraction Question


  • Please log in to reply
12 replies to this topic

#1 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 22 September 2006 - 04:13 AM

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...-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:


  $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";


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?

#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 22 September 2006 - 05:06 AM

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:

$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)"

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:

$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";

That will work since only date_out varies between each OR branch.  You can also combine the "greater" and "equal" into a single condition.

#3 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 26 September 2006 - 07:33 AM

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 : /



<? 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>



As shown, this prints the following:

fb_type | fb_color | stock01 | stock02 | bleh


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



#4 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 26 September 2006 - 10:09 PM

Still trying to figure this one out, I'm stumped at the moment.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 September 2006 - 10:31 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 26 September 2006 - 11:44 PM

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.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 06:35 PM

OK... could you post the table structure, and the columns you want subtracted?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 27 September 2006 - 10:03 PM

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



--
-- 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 ;


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

This one is "as stock01"


$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";


This one is "as stock02"


$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";


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.

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 10:36 PM

OK... what progress did you make with btherl's suggestions?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 27 September 2006 - 11:32 PM

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.

#11 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 October 2006 - 05:40 AM

/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 :)

#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 04 October 2006 - 07:37 PM

I'll see if I can find some time to play around with this.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#13 tommahh

tommahh
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 06 October 2006 - 12:03 AM

Thanks!  :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users