SQL Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL Question

  1. #1
    Join Date
    Oct 2001
    Posts
    3
    I have a one-to-many relationship between two tables, A and B, respectively. I need to sum a field from the many table (B), and group by a field in A (date) then B (UserID)... Here is an example of what I mean.

    TABLE A-------------
    A_ID
    DATE_ENTERED (Field to group by)

    TABLE B-------------
    B_ID
    A_ID (Foreign Key)
    VALUE (Field I need to sum)
    User_ID (Field to sub-group by)

    So the end query would return something like this:
    DATE USER_ID SUM(VALUE)
    12/01/2000 100 $500

    Each DATE/USER_ID row should be unique and be a sum of all entries in Table B for the DATE. By the way, I am looking to get back all data in the tables, aggregated this way.

    I took a shot at the SQL but I only get multiple rows for the DATE/User_ID combo.

    Any help would be greatly appreciated!

  2. #2
    Join Date
    Oct 2001
    Posts
    3

    One more thing...

    Sorry... I figured out what I think is my main problem... It believe it gives my multiple entries for a date because it is grouping according to time to.... How can I do a group for entries according to only the day?

    Hope this makes sense...

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    select a.DATE_ENTERED, b.User_ID , sum(b.VALUE)
    from tableA a, tableB b
    where a.A_ID = b.A_ID
    group by a.DATE_ENTERED, b.User_ID
    may be there are better ways but I can only think of this one now
    doesnt this work...?

  4. #4
    Join Date
    Oct 2001
    Posts
    3
    Pandito... YOU ARE THE MAN!

  5. #5
    Join Date
    Sep 2001
    Posts
    52

    Re: One more thing...

    Originally posted by zook9549
    Sorry... I figured out what I think is my main problem... It believe it gives my multiple entries for a date because it is grouping according to time to.... How can I do a group for entries according to only the day?

    Hope this makes sense...
    To group by day only use TRUNC(a.DATE_ENTERED). you will find group by day wise and ignoring the time factor

Posting Permissions

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


Click Here to Expand Forum to Full Width