# Thread: Combine 2 quieres (no union possible)

1. Registered User
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. :-)
Join Date
Jun 2003
Location
Posts
5,516
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```

3. SQL Consultant
Join Date
Apr 2002
Location
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```

4. Registered User
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. Registered User
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered User
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. Registered User
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
how/when are you going to actually learn SQL by having other do your homework assignments for you?

TO_DATE

TO_CHAR (datetime)

12. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>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

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

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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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
•