Joining two queries 4

[metalboy]

Hi all,

I have two queries to pull data which is used in one spreadsheet and i am having trouble putting them into one query:
SELECT COUNT(*), CAUSER, CADATE, CAFEE
FROM CALDET
WHERE (CADATE = 1041209 AND CATYPE = '2' AND CARETN = 'Y')
GROUP BY CAUSER

SELECT CAUSER, COUNT(*), CADATE, CAFEE
FROM CALDET
WHERE (CARETN = 'Y' AND CADATE = 1041209)
GROUP BY CAUSER, CADATE, CAFEE

is it possible to put these into one query? these are from an old data select system..

many thanks

Alex

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[buddafish]

Use a Union Select Query. This will take two tables and return one table with all the values.

Scottie

 

[metalboy]

i tried that and it will not work. both queries are looking at the same table...

my head will not work this out for me..(

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[r937]

your first query has a problem in that you are not grouping on all the columns properly

if you were able to "combine" these queries into one, then let's say user joe has data which would show up in both queries, would you want 2 rows returned with separate counts, or would you want the counts combined somehow?

there is nothing wrong with a UNION of two queries that look at the same table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[OhioSteve]

You can do a union query using two queries from the same table. I just did it.

Here is my data~
A B
1 3
2

Here is my SQL~
SELECT a
FROM Table9
UNION ALL select b as a
from Table9;

Here is my output~
A
1
2
3


Here are some tips for you:
1) You must write raw sql to do a union query. You cannot do it in design view.
2) "Union All" returns every record. "Union" returns every unique record.
3) Your situation is not exactly analogous to my example. Your syntax should say "...FROM [first query name]..." and "...FROM [second query name]..."

 

[PHV]

My guess:
SELECT CAUSER, COUNT(*) AS Num, '2' AS Type, CADATE, CAFEE
FROM CALDET
WHERE (CADATE = 1041209 AND CATYPE = '2' AND CARETN = 'Y')
GROUP BY CAUSER, CADATE, CAFEE
UNION SELECT CAUSER, COUNT(*), 'All', CADATE, CAFEE
FROM CALDET
WHERE (CARETN = 'Y' AND CADATE = 1041209)
GROUP BY CAUSER, CADATE, CAFEE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[metalboy]

thanks all! have a star each!

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[bburch1]

Is it possible to perform a union query where two queries use the same date range as parameters?

 

[r937]

bburch1, yes it is



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)