MySQL Forums
Forum List  »  Docs

Help with determining cost of goods sold
Posted by: Brizzle Mc' Nizzle
Date: August 03, 2012 07:07PM

I'm stuck on a query and, obviously, looking for some help. I am trying to keep track of the cost of the number of television wall mounts I have sold to individuals over a period of time. (The period of time currently being past to present) In doing so, I am trying to develop a query that selects the quantity of mounts sold from a table called `sales` and the cost of the inventory that was sold from a table called `inventory`.

Here are what the two tables look like: (both images leave out the `id` and `model` fields.

`sales`


`inventory`


The problem I am having is that if the number of sales exceed the quantity in the first row of the `inventory` table with a cost of $17.62, I don't know how to continue on to the second row so that I can subtract revenue from the new cost, which is $11.73. (The cost represents the price/mount) Anyone have an idea of how a query like this would work?

tl;dr: I need help developing a query that can determine the cost of the inventory I have sold using a first in, first out approach.

Example: If the quantity in the `sales` table was 15, this query would calculate that 1 of those mounts, the quantity in the first `inventory` row, cost me $17.62 and the remaining mounts would have cost me $11.73 since the first shipment of mounts are now all sold.

-- phpMyAdmin SQL Dump
-- version 3.4.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 03, 2012 at 08:17 PM
-- Server version: 5.1.63
-- PHP Version: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `tay_wallmount`
--

-- --------------------------------------------------------

--
-- Table structure for table `inventory`
--

CREATE TABLE IF NOT EXISTS `inventory` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `model` varchar(32) NOT NULL DEFAULT 'APTMM2BC',
  `quantity` int(32) NOT NULL,
  `cost` float NOT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `out_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `inventory`
--

INSERT INTO `inventory` (`id`, `model`, `quantity`, `cost`, `date`, `out_date`) VALUES
(1, 'M2APBCTM', 1, 17.62, '2012-07-28 18:30:13', NULL),
(2, 'M2APBCTM', 36, 11.73, '2012-08-02 05:01:07', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `sales`
--

CREATE TABLE IF NOT EXISTS `sales` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `model` varchar(32) DEFAULT 'APTMM2BC',
  `quantity` int(32) DEFAULT NULL,
  `price` int(32) NOT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=41 ;

--
-- Dumping data for table `sales`
--

INSERT INTO `sales` (`id`, `model`, `quantity`, `price`, `date`) VALUES
(1, 'M2APBCTM', 1, 35, '2012-07-30 19:29:57');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;



Edited 6 time(s). Last edit at 08/08/2012 07:02PM by Brizzle Mc' Nizzle.

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with determining cost of goods sold
7447
August 03, 2012 07:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.