SQL: how to use GROUP BY with UNION?

[JimmyTheHand]

Hi All,

this is an SQL question, rather than Access related. As a matter of fact, it has nothing to do with Access, but I think this is the best choice of forums.

So the outline is this. In an analytical laboratory there's a database to store samples, tests and results. I need to retrieve the average time (called "transit time") that was needed to complete each individual tests over a year, and display this average value for each year, separately.
Individual transit times are calculated as the difference between the time of test completion and time of sample login. The current year is always the year of sample login, even if test completion is postponed until next year.

The twist is that there are two sets of DB tables, an active (sample, test) and an archived (c_sample, c_test) set. Their definitions are identical, but one stores recent data, and is used everyday, the other stores old data, and is used rarely. I need data from both, at the same time.

I figured, what I needed to do was making two selects, grouping them by the year value, and then making a union of them. Here's what I did:



SELECT avg(test.date_completed-sample.login_date) as transit, to_char(to_date(sample.login_date), 'YYYY') as wYear
FROM sample JOIN test
ON sample.id_numeric = test.sample
GROUP BY wYear

UNION

SELECT avg(c_test.date_completed-c_sample.login_date) as transit, to_char(to_date(c_sample.login_date), 'YYYY') as wYear
FROM c_sample JOIN c_test
ON c_sample.id_numeric = c_test.sample
GROUP BY wYear
In my best knowledge the above SELECT should work, but it doesn't. Anyone knows why? The error message is:
ORA - 00904: "WYEAR": invalid identifier
Remark: The query does work without the GROUP BY clauses. But, of course, it's not what I need.

Thanks,

Jimmy

EDIT:
The same problem (with almost identical description) has been cross-posted on TSG forum:
http://forums.techguy.org/development/539870-sql-how-use-group-union.html#post4405515

[Bob Phillips]

Have you tried?


GROUP By YEAR(sample.logion_date)

[Bob Phillips]

Or maybe


SELECT AVG(test.date_completed-sample.login_date) AS transit,
TRUNC(sample.login_date, 'YEAR') AS wYear
FROM sample JOIN test
ON sample.id_numeric = test.sample
GROUP BY TRUNC(sample.login_date, 'YEAR')

[JimmyTheHand]

Thanks Bob,

the second tip gave me e.g. 2005.01.01 instead of 2005 as group field, but other than that it works fine. On TSG forum I receied another solution, which doesn't have this little glitch, and I decided to continue the thread there, and mark this one as solved. Thanks again. :thumb

Jimmy

[Bob Phillips]

I think we are applying the same principle, but I am not an Oracle developer so I was guessing a bit with that.

When I did a bit more looking, it seemed that Oracle doesn't support the YEAR function, which is why I added the TRUNC attempt.

It just seemed superfluous to me to convert a date field to date then to char, so I guess that your login date is not a date but a string then?

[JimmyTheHand]

It just seemed superfluous to me to convert a date field to date then to char, so I guess that your login date is not a date but a string then?

That's right. It's varchar2, as almost everything in that DB. Even numeric fields are stored as varchar2. I've never been able to put together a (working) WHERE clause with such date fields, unless I used to_date function. Maybe I could extract the year in some other way, too, but this seemed obvious to me.

[Bob Phillips]

What a waste!

I think if it is char, you don't have many options do you?

Do you have string functions, such as LEFT?

[JimmyTheHand]

Not sure what you mean by many options. There are lots of string functions available, and the are very versatile... Why, you might even be able to transform a heap of dates to an Eiffel-tower :)

Anyway, I found that to_date was not needed where I put it, and in the meantime I upgraded the query with some new functionality.

The final SELECT is this:

SELECT AVG(DC-RD) as "Transit time", to_char(LD,'YYYY') as "Year", count(LD) as "Sample Count"
FROM (
SELECT test.date_completed as DC, sample.recd_date as RD, sample.login_date as LD
FROM sample
JOIN test ON sample.id_numeric = test.sample
JOIN versioned_analysis ON test.analysis = versioned_analysis.identity
WHERE versioned_analysis.group_id = 'Department1' and (test.status = 'completed')

UNION ALL

SELECT c_test.date_completed as DC, c_sample.recd_date as RD, c_sample.login_date as LD
FROM c_sample
JOIN c_test ON c_sample.id_numeric = c_test.sample
JOIN versioned_analysis ON c_test.analysis = versioned_analysis.identity
WHERE versioned_analysis.group_id = 'Department1' and (c_test.status = 'completed')
)
GROUP BY to_char(LD,'YYYY')


The case is now really solved. Thx for all your help.

Jimmy