#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0

    Question Sql Query help..


    Hi Guys,

    I need help with SQL query. I am trying to achieve result data as subtraction of 2 query

    Query 1 - select sum(frequency) from pbarcmas where transtype = 1 and consultdate between {d'2011-07-01'} and {d'2012-06-30'} and deleted = 0 and amount =>0 group by itemcode;

    Query 2 - select sum(frequency) from pbarcmas where transtype = 1 and consultdate between {d'2011-07-01'} and {d'2012-06-30'} and deleted = 0 and amount <0 group by itemcode;

    So basically i need result of query1.(SumFrequency) - query 2.(SumFrequency).
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    both of your queries are useless because you don't have itemcode in the SELECT clause

    with GROUP BY on itemcode, but only SUM(frequency) in the SELECT clause, the results would look something like this --

    123
    582
    9
    37

    and there's no way to tell which sum belongs with which itemcode
    Code:
    SELECT itemcode
         , pos
         , neg
         , pos - neg AS balance
      FROM ( SELECT itemcode
                  , SUM(CASE WHEN amount => 0 THEN frequency ELSE NULL END) AS pos
                  , SUM(CASE WHEN amount  < 0 THEN frequency ELSE NULL END) AS neg 
               FROM pbarcmas 
              WHERE transtype = 1 
                AND consultdate BETWEEN {d'2011-07-01'} AND {d'2012-06-30'} 
                AND deleted = 0 
             GROUP 
                 BY itemcode ) AS data
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by r937
    both of your queries are useless because you don't have itemcode in the SELECT clause

    with GROUP BY on itemcode, but only SUM(frequency) in the SELECT clause, the results would look something like this --

    123
    582
    9
    37

    and there's no way to tell which sum belongs with which itemcode
    Code:
    SELECT itemcode
         , pos
         , neg
         , pos - neg AS balance
      FROM ( SELECT itemcode
                  , SUM(CASE WHEN amount => 0 THEN frequency ELSE NULL END) AS pos
                  , SUM(CASE WHEN amount  < 0 THEN frequency ELSE NULL END) AS neg 
               FROM pbarcmas 
              WHERE transtype = 1 
                AND consultdate BETWEEN {d'2011-07-01'} AND {d'2012-06-30'} 
                AND deleted = 0 
             GROUP 
                 BY itemcode ) AS data
    yup i know that.. itemcode is no problem for me.. my original query is

    Select itemcode, sum(amount), sum(frequency) from pbarcmas where Transtype = 1 and consultdate between {d'2011-07-01'} and {d'2012-06-30'} and deleted = 0 and amount >= 0 group by itemcode;

    but issue i have i do not want sum(frequency) for any itemcode where amount is less then zero ( refund or return etc) and alos take that amount from sum(amount) too..
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    so did you try my query?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by r937
    so did you try my query?
    Yup i did.. i am getting error message invalid construction..
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by sam_vish
    Yup i did.. i am getting error message invalid construction..
    o rly?

    can you post the exact query you ran, as well as the exact error message?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by r937
    o rly?

    can you post the exact query you ran, as well as the exact error message?
    Sql Query is same as yours..

    SELECT itemcode
    , pos
    , neg
    , pos - neg AS balance
    FROM ( SELECT itemcode
    , SUM(CASE WHEN amount => 0 THEN frequency ELSE NULL END) AS pos
    , SUM(CASE WHEN amount < 0 THEN frequency ELSE NULL END) AS neg
    FROM pbarcmas
    WHERE transtype = 1
    AND consultdate BETWEEN {d'2011-07-01'} AND {d'2012-06-30'}
    AND deleted = 0
    GROUP
    BY itemcode ) AS data

    error message i am getting

    00:52:35 [SELECT - 0 row(s), 0.000 secs] [Error Code: -12104, SQL State: S1000] [MIMER][ODBC Mimer Driver][Mimer SQL]Invalid construction
    ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

    it works fine if i try to run that bit of query with correct result

    SELECT itemcode
    , SUM(CASE WHEN amount => 0 THEN frequency ELSE NULL END) AS pos
    , SUM(CASE WHEN amount < 0 THEN frequency ELSE NULL END) AS neg
    FROM pbarcmas
    WHERE transtype = 1
    AND consultdate BETWEEN {d'2011-07-01'} AND {d'2012-06-30'}
    AND deleted = 0
    GROUP
    BY itemcode
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    well, it looks like mimer can't handle derived tables

    can't help you with mimer (this is the microsoft sql server forum)
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by r937
    well, it looks like mimer can't handle derived tables

    can't help you with mimer (this is the microsoft sql server forum)
    no worries mate.. It helped me alot anyways.. Thx..

IMN logo majestic logo threadwatch logo seochat tools logo