CODEWITHSUNDEEP
mysql
Chamara Abeysekara
Chamara Abeysekara

Reputation: 1322

MySQL combining two queries together

this my first question and I'm still learning Mysql so the problem I have is to combine two queries together

1st query :

SELECT
     stocks.Stock_Trans_No
     stocks.Stock_Date_Time,
     stocks.Stock_Trans_Type,
     stocks.Stock_Trans_Ref_No,
     stocks.Stock_Trans_Item_Code,
     stocks.Stock_Trans_Qty,
     stocks.Item_Description

FROM stocks

WHERE

DATE_FORMAT( stocks.Stock_Date_Time, '%Y%m%d') BETWEEN   DATE_FORMAT('2015-09-01', '%Y%m%d') AND   DATE_FORMAT('2015-11-01', '%Y%m%d')  GROUP BY   stocks.`Stock_Trans_Item_Code`

2nd query:

SELECT 
SUM( stocks.Stock_Trans_Qty) 
FROM  stocks
WHERE 
DATE_FORMAT( stocks.Stock_Date_Time, '%Y%m%d') > '2015-09-01' 
GROUP BY stocks.`Stock_Trans_Item_Code`

how do I get these together. I'm still Studying so, please be kind enough to be as simple as you can! thank you

Upvotes: 0

Views: 70

Answers (2)

Wajih
Wajih

Reputation: 4383

Try this one

SELECT s1.*,
(SELECT SUM(s2.Stock_Trans_Qty)
 FROM stocks as s2
 WHERE
  s2.Stock_Trans_Item_Code = s1.Stock_Trans_Item_Code AND
  DATE_FORMAT(s2.Stock_Date_Time, '%Y%m%d') > '2015-09-01'
 ) Total
FROM stocks as s1
WHERE
 DATE_FORMAT(s1.Stock_Date_Time, '%Y%m%d') BETWEEN DATE_FORMAT('2015-09-01', '%Y%m%d') AND DATE_FORMAT('2016-11-01', '%Y%m%d')
GROUP BY s1.`Stock_Trans_Item_Code`

Upvotes: 0

Ajay Makwana
Ajay Makwana

Reputation: 2372

Try this..

SELECT
SUM( IF(DATE_FORMAT( stocks.Stock_Date_Time, '%Y%m%d') > '2015-09-01',stocks.Stock_Trans_Qty,0)) AS Stock_Trans_Qty_Sum
 stocks.Stock_Trans_No
 stocks.Stock_Date_Time,
 stocks.Stock_Trans_Type,
 stocks.Stock_Trans_Ref_No,
 stocks.Stock_Trans_Item_Code,
 stocks.Stock_Trans_Qty,
 stocks.Item_Description
FROM stocks
WHERE
DATE_FORMAT( stocks.Stock_Date_Time, '%Y%m%d') BETWEEN   DATE_FORMAT('2015-09-01', '%Y%m%d') AND   DATE_FORMAT('2015-11-01', '%Y%m%d')  GROUP BY   stocks.`Stock_Trans_Item_Code`

Upvotes: 2

Related Questions