Results 1 to 7 of 7

Thread: Group by query

  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: Group by query

    I have this table .....
    Code:
    AID   NAME  DATE      AMOUNT TYPE
    1001  ABC  1/1/2006    120       AC
    1001  ABC  1/1/2006     23        AC
    
    1001  BC    1/1/2006      12       AC
    1001  DC    1/1/2006      22       TR
    
    1002  ZX    1/1/2006      21        DR
    
    1003  ABC  1/1/2006       23       AC
    1003  VF    1/1/2006       44       AC
    Now I want a query which will give a result set of - between a specific date
    i.e between 1/1/2006- 1/2/2006

    Code:
    AID   NAME  AC_AMOUNT TR_AMOUNT  DR_AMOUNT 
    1001 ABC     143                0                 0
    1001 BC        12                0                   0
    1001 DC         0                 22                 0
    1002 ZX        0                    0                 21
    
    ----
    1003 ABC      23                 0                0
    One row for each name,that is group up by will be on AID and NAME.

    Any help will be greatly appreciated...
    Last edited by rudra; 10-11-06 at 08:29.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    create columns in the query using the CASE operator on the "Type" column.
    use group by to get the desired result..
    Will not provide any ready made query since its for you to build 1.
    Hope this helps.
    In GOD we believe. Everything else we Test!

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by wash
    create columns in the query using the CASE operator on the "Type" column.
    use group by to get the desired result..
    Will not provide any ready made query since its for you to build 1.
    Hope this helps.
    Sorry ,I think that will not work at all.I mean if you use case in that case you have to use group by on AID,NAME,Type.
    But That will give you multiple rows, which I don't want....
    I think its not so easy man...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rudra
    I think its not so easy man...
    yes it is, it is very easy
    Code:
    select AID   
         , NAME
         , sum(case when TYPE='AC'
                    then AMOUNT else 0 end) as AC_AMOUNT  
         , sum(case when TYPE='TR'
                    then AMOUNT else 0 end) as TR_AMOUNT  
         , sum(case when TYPE='DR'
                    then AMOUNT else 0 end) as DR_AMOUNT  
      from daTable
     where DATE between '1/1/2006' and '1/2/2006'
    group
        by AID   
         , NAME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
    Thanks Rudy for correcting me.

    Hope now this helps you!!!
    In GOD we believe. Everything else we Test!

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by r937
    yes it is, it is very easy
    Code:
    select AID   
         , NAME
         , sum(case when TYPE='AC'
                    then AMOUNT else 0 end) as AC_AMOUNT  
         , sum(case when TYPE='TR'
                    then AMOUNT else 0 end) as TR_AMOUNT  
         , sum(case when TYPE='DR'
                    then AMOUNT else 0 end) as DR_AMOUNT  
      from daTable
     where DATE between '1/1/2006' and '1/2/2006'
    group
        by AID   
         , NAME
    Oh my God !! Yes thats it ...I haven't thought that ....
    If you put the sum within the case, you have to use Type in Group by...hmmm thats it
    Superb !!
    You have put the case within Sum(), WOW!!

    Rudy you are great !!
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by wash
    Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
    Thanks Rudy for correcting me.

    Hope now this helps you!!!
    Anyways, Thanks Wash for your help.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •