Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I Have two queries Query 1:

SELECT 
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL SOLD'
from
    tbl_sales s,
    tbl_matentry e
WHERE
    e.eid = s.item
GROUP By e.eid ;

Result 1:

+---------+-------------+---------------+
|     eid |    ITEM     |  TOTAL SOLD   |
+---------+-------------+---------------+
|       1 | rupa        |             5 |
|       2 | pan america |             3 |
|       3 | John Player |            10 |
|       4 | classmate   |            11 |
|       5 | lepakshi    |            55 |
|       6 | lee         |            14 |
|       7 | puma        |             9 |
+---------+-------------+---------------+

Query 2:

SELECT 
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL STOCK'
from
    tbl_purchases p,
    tbl_matentry e
WHERE
     e.eid = p.item
GROUP By e.eid ;

Result 2:

+---------+-------------+--------------------+
|     eid |    ITEM     |        TOTAL STOCK |
+---------+-------------+--------------------+
|       1 | rupa        |                 41 |
|       2 | pan america |                 45 |
|       3 | John Player |                 32 |
|       4 | classmate   |                 75 |
|       5 | lepakshi    |                 56 |
|       6 | lee         |                 65 |
|       7 | puma        |                 50 |
+---------+-------------+--------------------+

When I combine both sql statements into single statement I am getting incorrect output

So Far i have tried this

SELECT 
e.eid,
e.item as 'ITEM',
SUM(p.qty) as 'TOTAL STOCK',
SUM(s.qty) as ' TOATL SOLD',
(SUM(p.qty)-SUM(s.qty)) as 'BALANCE STOCK'
FROM
tbl_matentry e
    LEFT OUTER JOIN
tbl_purchases p ON p.item = e.eid
    LEFT OUTER JOIN
tbl_sales s ON s.item=e.eid
GROUP BY e.eid ORDER BY e.eid

+-----+-------------+-------+------+---------+
| eid |    ITEM     | STOCK | SOLD | BALANCE |
+-----+-------------+-------+------+---------+
|   1 | rupa        |   123 |   15 |     108 |
|   2 | pan america |    90 |    6 |      84 |
|   3 | John Player |    64 |   20 |      44 |
|   4 | classmate   |   150 |   22 |     128 |
|   5 | lepakshi    |   112 |  110 |       2 |
|   6 | lee         |   130 |   28 |     102 |
|   7 | puma        |   100 |   18 |      82 |
+-----+-------------+-------+------+---------+

How to do i get correct result? Where is mistake/error in my query.Please review my Query and help me to get correct data.

My Expected Output is

+---------+-------------+------------------+-----------------+------------------+
|     eid |    ITEM     |      TOTAL STOCK |   TOTAL SOLD    |  BALANCE STOCK   |
+---------+-------------+------------------+-----------------+------------------+
|       1 | rupa        |               41 |               5 |               36 |
|       2 | pan america |               45 |               3 |               42 |
|       3 | John Player |               32 |              10 |               22 |
|       4 | classmate   |               75 |              11 |               64 |
|       5 | lepakshi    |               56 |              55 |                1 |
|       6 | lee         |               65 |              14 |               51 |
|       7 | puma        |               50 |               9 |               41 |
+---------+-------------+------------------+-----------------+------------------+
share|improve this question
    
Could you tell me how you are editing the post with beautification i mean like above table structures and all I have tried while i am posting this question @bluefeet –  Narendar_CH Aug 9 '13 at 14:33
    
    
Thank you @bluefeet –  Narendar_CH Aug 9 '13 at 14:37
add comment

2 Answers

up vote 5 down vote accepted

The problem that you are having is most likely coming from the tbl_sales because your item can appear in the table more than once. When you then JOIN your tables together, you are returning the qty from tbl_purchases for each row in tbl_sales.

You can see this behavior by performing a SELECT on your tables:

SELECT e.*, p.qty purQty, s.qty salesQty
FROM tbl_matentry e
LEFT OUTER JOIN tbl_purchases p 
 ON p.item = e.eid
LEFT OUTER JOIN tbl_sales s 
  ON s.item=e.eid
ORDER BY e.eid;

See Demo. You will see in my sample, that the rupa qty appears twice because there are two entries in tbl_sales.

One way to get the result would be to calculate the TotalSold and TotalStock in subqueries:

select e.eid, 
  e.item,
  coalesce(p.TotalStock, 0) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(p.TotalStock, 0) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
left join
(
  select item, sum(qty) TotalStock
  from tbl_purchases
  group by item
) p
  on e.eid = p.item;

See SQL Fiddle with Demo.

This could also be written using a single subquery to tbl_sales:

select e.eid, 
  e.item,
  sum(p.qty) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(sum(p.qty)) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join tbl_purchases p
  on e.eid = p.item
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
group by e.eid, e.item, TotalSold;

See SQL Fiddle with Demo

share|improve this answer
    
Thank you You are super. –  Narendar_CH Aug 9 '13 at 13:23
    
Are you sure this is correct ? You don't have join in subquery. –  IvanP Aug 9 '13 at 13:27
    
@IvanP Yes it is correct, you don't have to join in the subquery, but as I said this is one way to get the result. –  bluefeet Aug 9 '13 at 13:30
    
You are right. Though it looks to me it might be quicker with joins depending on data. –  IvanP Aug 9 '13 at 13:34
add comment

Simplest solution would be to UNION them. As to error, you are joining sales and purchases, sort of multiplying apples and oranges ( one purchase is multiplied by 3 sales, or vice versa, in first line of your example , rupa 123 - 15 as opposed to 41 - 5 ).

SELECT eid, item, SUM(tsl) AS 'TOTAL SOLD', SUM(tsk) AS  'TOTAL STOCK', SUM(tsk) - SUM(tsl) AS 'BALANCE STOCK'
FROM
(
    SELECT e.eid, e.item AS 'ITEM', SUM(s.qty) AS tsl, 0 AS tsk
    FROM tbl_sales s JOIN tbl_matentry e ON e.eid = s.item
    GROUP BY e.eid
    UNION
    SELECT e.eid, e.item AS 'ITEM', 0 AS tsl, SUM(p.qty) AS tsk
    FROM tbl_purchases p JOIN tbl_matentry e ON e.eid = p.item
    GROUP BY e.eid
) x
GROUP BY eid
share|improve this answer
    
Thank you very much Now I have two options to choose –  Narendar_CH Aug 9 '13 at 13:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.