title: SQL query is not returning desired value
body: <p>I have three Tables as follows:</p>
  
  <pre><code>CREATE Table [Product]( 
  [product_id] int primary key,
  [name] varchar(100) not null,
  [date] varchar(20) not null,
  [quantity] int not null,
  [stock] int not null,
  [category] varchar(50) not null,
  [unitPrice] int not null,
  [vendor_id] int not null
  )
  
  
  create table [distribution](
  [distribution_id] int primary key,
  [showroom] varchar(50) not null,
  [quantity] int not null,
  [date] varchar(20) not null,
  [unitPrice] int not null,
  [product_id] int not null
  )
  
  create table [sales](
  [sales_id] int primary key,
  [product_id] int not null, 
  [date] varchar(20) not null,
  [time] varchar(15) not null,
  [quantitiy] int not null,
  [cash] int not null,
  [branch] varchar(50) not null
  )
  </code></pre>
  
  <p>Now I want to make a query that will return for each Product ID</p>
  
  <pre><code>[product].product_id, 
  [product].unitPrice as 'pUnitPrice', 
  [product].quantity, 
  [product].unitPrice*[product].quantity as "stockTotal",
  SUM([sales].quantitiy) as 'salesUnit', 
  [distribution].unitPrice as 'sUnitPrice',
  SUM([sales].quantitiy)*[distribution].unitPrice as 'saleTotal', 
  [product].quantity-SUM([sales].quantitiy) as 'balance'
  </code></pre>
  
  <p>I am doing my project in ASP.NET with Microsoft SQL Server 2008 R2. I have made a query(appended) but that results wrong may it is checking all the data for one product_id. I am very much disapponted...Please help me if anyone has time....Please please...</p>
  
  <p>My query: </p>
  
  <pre><code>SELECT 
      [sales].product_id, 
      [product].unitPrice                             as 'pUnitPrice', 
      [product].quantity                              as 'stock', 
      [product].unitPrice * [product].quantity        as "stockTotal", 
      SUM([sales].quantitiy)                          as 'salesUnit',
      [distribution].unitPrice                        as 'sUnitPrice',
      SUM([sales].quantitiy)*[distribution].unitPrice as 'saleTotal',
      [product].quantity-SUM([sales].quantitiy)       as 'balance'
  from sales 
  JOIN product        ON sales.product_id     = product.product_id
  JOIN [distribution] ON [product].product_id = [distribution].product_id 
  group by [sales].product_id, 
           [product].unitPrice, 
           [product].quantity, 
           [distribution].unitPrice
  order by [sales].product_id 
  </code></pre>
  
  <p>Only the</p>
  
  <pre><code>SUM([sales].quantity)
  </code></pre>
  
  <p>gives the error. It just added up to three times. Say, the desired quantity is 4, it became 12, just like for every product_id....</p>
  
  <p>The values in product</p>
  
  <p>(product_id, name, date ,quantity, stock, category, unitPrice, vendor_id)</p>
  
  <p>(1,HP, 2013-03-15, 10, 6, Laptop, 55000, 2)</p>
  
  <p>The values in <code>Distribution</code> table:</p>
  
  <pre><code>distribution_id   showroom   quantity      date     unitPrice    product_id
        1            Ritzy1        2      2013-03-02    55000          1
        2            Ritzy2        2      2013-03-02    55000          1
        3            Ritzy3        2      2013-03-02    55000          1
  </code></pre>
  
  <p>The values in <code>Sales</code> table:</p>
  
  <pre><code> sales_id   product_id       date       time      quantitiy    cash     branch
      1           1         2013-03-29  7:26:22 PM      2   110000    Ritzy1
  </code></pre>
  
  <p>My query result</p>
  
  <pre><code>(product_id,    pUnitPrice, stock,  stockTotal, salesUnit,  sUnitPrice, saleTotal,  balance)
  
  
  (1, 50000,  10, 500000, **6**,  55000,  330000, 4)
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code> product_id  pUnitPrice  stock  stockTotal  salesUnit  sUnitPrice  saleTotal  balance
       1         50000       10     500000        2         55000      110000      8
  </code></pre>