Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Unanswered: Combine 2 quieres (no union possible)

    hey sql-oracle fans

    i'm quite a newbie with sql and especially oracle ...but maybe you can help me out

    here's some background info: i have to count how many trucks are leaving from a certain place ("start") and then i have to find out how many of those trucks leaving from the one certain place are marked as urgent (urgent = 1 oder = 0). since my table is huge and joining it with itself takes forever i created just a copy (tbla & tblb) - so don't be confused with my where-statement.

    SELECT tbla.start, COUNT(tbla.start)
    FROM tbla, tblb
    WHERE tbla.id=tblb.id
    GROUP BY tbla.start
    ORDER BY tbla.start ASC

    output example:
    chicago - 10
    l.a. - 45
    n.y. - 32

    SELECT tbla.start, COUNT (tbla.start)
    FROM tbla, tblb
    WHERE tbla.id=tblb.id
    AND tbla.urgent=1
    GROUP BY tbla.start
    ORDER BY tbla.start ASC
    (the only difference if tbla.urgent=1, but that's crucial)

    output example:
    chicago - 2
    l.a. - 28
    n.y. - 12

    what i need as an output is:
    chicago - 10 - 2
    l.a. - 45 - 28
    n.y. - 32 - 12

    if i need to combine both queries in such a what that i get three columns and each count is grouped by the city.

    this is what i have figured out so far...

    SELECT tbla.start, count(tbla.start),
    (SELECT count (tbla.start) FROM tbla, tblb WHERE tbla.id=tblb.id AND tbla.urgent=1) AS urg)
    FROM tbla, tblb
    WHERE tbla.id=tblb.id
    GROUP BY tbla.start
    ORDER BY tbla.start ASC

    ...but it doesn't show the number of urgent trucks per city but the sum (in my example it would be 2+28+12=42) for each city
    output example which is wrong (but which i get):
    chicago - 10 - 42
    l.a. - 45 - 42
    n.y. - 32 - 42


    hmmm...i hope you understand what my problem is. and plz, plz, plz answer me. i'm on this for days now and can't figure it out

    if you have an answer please make it explicit (stuff like "use a temp table and then join it" doesn't help me too much since i'm too new on the subject) - THANX!!

    kisses :-*

    btw: i'm using oracle 8.1.7

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    SELECT tbla.start, COUNT(tbla.start), COUNT(DECODE(tbla.urgent, 1, 1))
    FROM tbla, tblb
    WHERE tbla.id=tblb.id
    GROUP BY tbla.start
    ORDER BY tbla.start ASC
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT tbla.start
         , COUNT(*)                           AS total_count
         , COUNT(CASE WHEN tbla.urgent = 1
                      THEN 937 ELSE NULL END) AS urgent_count
      FROM tbla
    INNER
      JOIN tblb 
        ON tblb.id = tbla.id
    GROUP 
        BY tbla.start
    ORDER 
        BY tbla.start ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2009
    Posts
    8
    you guys - THANK YOU SO MUCH!!!

    it works perfect!! if used the upper one because i also included ratio_to_report(count(tbla.start)) to get some percentages and that was just easier...

    and thank you especially for really spelling everything out

  5. #5
    Join Date
    Jun 2009
    Posts
    8
    by the way...maybe you can also help me out with another (similar) problem...

    my table has
    start - finish
    A - B
    A - B
    A - C
    A - D
    B - C
    B - F
    B - F
    B - F

    and I would like to get this output
    start - finish - number of trips - percentage
    A - B - 2 - 25%
    A - C - 1 - 12,5%
    A - D - 1 - 12,5%
    B - C - 1 - 12,5%
    B - F - 3 - 37,5%

    I have to find a way to connect start and finish and then count it. I thought using a view or temporary table but i can't figure it out...any ideas?

    oh man...I'm still so glad you could help me on the previous one

    kisses :-*

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This might be one way to do that:
    Code:
    SQL> select * from test order by sta, fin;
    
    S F
    - -
    A B
    A B
    A C
    A D
    B C
    B F
    B F
    B F
    
    8 rows selected.
    
    SQL> select sta, fin, count(*) cnt,
      2    count(*) / (select count(*) from test) * 100 pct
      3  from test
      4  group by sta, fin
      5  order by sta, fin;
    
    S F        CNT        PCT
    - - ---------- ----------
    A B          2         25
    A C          1       12.5
    A D          1       12.5
    B C          1       12.5
    B F          3       37.5
    
    SQL>

  7. #7
    Join Date
    Jun 2009
    Posts
    8
    thanx a lot!!!

    actually pretty easy thing to do...guess to much thinking on the previous problem got me all messed up

    kisses :-*

  8. #8
    Join Date
    Jun 2009
    Posts
    8
    ok...final question

    I need to group the trucks arriving based on the time slot. My output has to look like the following:
    from - to - number of trucks arriving
    00:00 - 00:29 - 1
    00:30 - 00:59 - 4
    01:00 - 01:29 - 12
    ...
    23:30 - 23:59 - 3

    My colum "time" is in "DATE" format.

    kisses :-*

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Final question requires final answer: how about doing at least a little bit of homework by yourself? What did you manage to do so far? Why aren't you satisfied with it? Post your attempt(s), explain what it (they) do and someone will take a look and help improve your solution.

  10. #10
    Join Date
    Jun 2009
    Posts
    8
    hey

    sorry, my mistake. But all my ideas are no good at all.

    I thought about using dbms or analyze, but that seems to either not work (getting error messages) or the entire connection breaks down. I guess those functions are for entire tables and not just one column.

    So my idea was now to make a hugh union-statement:

    SELECT time
    FROM tbl
    WHERE time BETWEEN time AND time+29

    UNION

    SELECT time
    FROM tbl
    WHERE time BETWEEN time+30 AND time+59

    UNION

    SELECT time
    FROM tbl
    WHERE time BETWEEN time+60 AND time+89
    ...

    But it is not working out.

    First of all I can't figure out how to ignore the date (e.g. I have to make a time-histogram for April 2009, but I don't care about the specific dates, only about the time slots).

    Second, I don't know how to get a count into it (counting the number of trucks leaving between 00:00 and 00:29).
    Here is my possible solution to it (but doesn't work either, because I don't want to group it by date, but by time slot)

    SELECT time, COUNT (time)
    FROM tbl
    WHERE time BETWEEN time AND time+29
    GROUP BY time

    The entire day I've been reading webpages and books about date-functions and formats - but all in vain. Maybe there is some easy command I just haven't come across yet?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    how/when are you going to actually learn SQL by having other do your homework assignments for you?

    TO_DATE

    TO_CHAR (datetime)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jun 2009
    Posts
    8
    Thanx a lot for you answer!
    I have already read all this stuff but couldn't really apply it in such a way that it worked out the way I want it to.

    Why do you guys always think somebody is not seriously trying by themselves? If I could figure it out alone, I wouldn't be posting questions! But that's what a forum is for! And like I already wrote in my very first post - SQL is totally new to me. So I've been reading all the time books, webpages, forums to figure it out by myself. I can do all the easy queries and text-book examples...but now I got stuck. What's the problem with asking questions? You know, sometimes you are thinking so hard that you just miss the solution (like my second question - yeah, I admit, it was unnecessary to post it).

    And just for the record - the past three weeks I've been working five days a week trying to figure it out by myself (next to doing all the easier queries). So asking for help is kinda justified, don't you think?

    This is not supposed to sound rude, but you should not always presume people don't try themselves first!

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >WHERE time BETWEEN time+60 AND time+89

    Here are a couple of free clues.

    With Oracle the default unit of time is DAY (a.k.a. 24 hours); +60 adds 60 days to TIME.

    It is a very, very bad idea to use KEYWORDS as field names.

    select keyword from v$RESERVED_WORDS where keyword like 'T%' order by 1;

    TIME is a KEYWORD
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Jun 2009
    Posts
    8
    I know I just used "time" to explain it. The keyword is actually mtq.

    so it had do be
    WHERE mtq BETWEEN mtq+(1/(24) AND mtq+(1/(24*60))*1.5
    in order to get an interval between 01:00 am and 01:29 am?

  15. #15
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like that:
    Code:
    SQL> with test as
      2    (select trunc(sysdate) mtq from dual)
      3  select mtq + 1/24,
      4         mtq + (1/(24 * 60)) * 1.5
      5  from test;
    
    MTQ+1/24            MTQ+(1/(24*60))*1.5
    ------------------- -------------------
    09.06.2009 01:00:00 09.06.2009 00:01:30
    
    SQL>

Posting Permissions

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