Help me construct this count distinct query!

[S Davis]

Hi guys,

I'm at my wits end, and just can't get what I want out of this query.

After some preparation, this is where I am at:

OBJ_CODE OBJ_MRC OBJ_CATEGORY SumOfREA_DIFF REA_DATES
2101 176 E40LF 3647.7 2005-10-28
2101 176 E40LF 245.9 2005-11-04
2101 176 E40LF 141 2005-11-10
2101 176 E40LF 85.1 2005-11-18


What I want to do in the end is have a count of all similar OBJ_CODE,
and for that count a sum of SumOfREA_DIFF, between two set dates. This
will be pushed back into excel where I will use a parameter query to
define the dates from a formula. So, assuming that the information I
want is between the dates of 2005-10-28 and 2005-11-18, what I would
like to see in the end would be:

OBJ_CODE OBJ_MRC OBJ_CATEGORY SumOfSumOfREA_DIFF
4 176 E40LF 4119.7

I'm hoping you can understand what I am trying to describe! Any
questions, feel free to email me direct at (e-mail address removed), or
post to this thread. I will post back with the solution once it has
been figured out.

Thank you!

 

[S Davis]

Sorry, I managed to post this into the wrong forum. I am building this
query in Access, with the final calculation needing to be made in
MSQuery to get the parameters built in for the date ranges.

 

[Lynn Trapp]

Give this a try.

SELECT Count(OBJ_CODE), OBJ_MRC, OBJ_CATEGORY, Sum(SumOfREA_DIFF) As
SumOfSumOfREA_DIFF
Where REA_DATES >= #2005-10-28# And REA_DATES <= #2005-11-18#
GROUP BY OBJ_MRC, OBJ_CATEGORY;

 

[S Davis]

Thanks, but that's not really what I'm trying to get at here. My fault
with the muddled description.

I'll post up the SQL from a working query. This SQL is from 'MSQuery'
under Excel2003, and works when using an R5 Merant driver to read our
database. We just did a server switch and are not trying to convert
everything over to run exclusively with the new database (DataStream
7.10) using the 'Microsoft ODBC for Oracle' drivers.

The end result is that while most queries can be converted, when I am
presented with a query that contains parameters, I am left with a huge
challenge. Just bringing the SQL across is not allowed with the
parameters. In addition, due to some conflicts between the R5 and ODBC
drivers, the coding of the old SQL has to be converted back.
Essentially this means rewriting the entire query until something
works. Enter: me

Here is the old, working SQL using an R5 Merant driver:

SELECT R5OBJECTS.OBJ_MRC, R5OBJECTS.OBJ_CATEGORY,
Sum(R5READINGS.REA_DIFF), Count(Distinct R5OBJECTS.OBJ_CODE)
FROM DS7USER.R5METERS R5METERS, DS7USER.R5OBJECTS R5OBJECTS,
DS7USER.R5READINGS R5READINGS
WHERE R5METERS.MET_CODE = R5READINGS.REA_METER AND R5OBJECTS.OBJ_CODE =
R5READINGS.REA_OBJECT AND ((to_char(REA_DATE,'YYYY-MM-DD')>=? And
to_char(REA_DATE,'YYYY-MM-DD')<=?) AND (R5OBJECTS.OBJ_CODE>='1000' And
R5OBJECTS.OBJ_CODE<='9999') AND (R5OBJECTS.OBJ_CLASS='DESL') AND
(R5OBJECTS.OBJ_STATUS='I'))
GROUP BY R5OBJECTS.OBJ_MRC, R5OBJECTS.OBJ_CATEGORY
HAVING (R5OBJECTS.OBJ_MRC In ('177','176','178','179','180','307'))

You'll notice a few things:
Count(Distinct works. This does not work in vanilla MSQuery talk.
to_char(. This was necessary to get the database data into a format
that was recognizeable by the different driver. I can replace this with
the Format(. option to eliminate minutes and seconds from dates and
allow easier grouping.

=?. These are parameters. Remember that this query is being run by MSQuery and so this would prompt excel for a Parameter value (a cell with a date in it). When copying the SQL over, this no longer works.

So my goal is to take that SQL and its functionality, convert it so
that it works with MS ODBC for Oracle, and output something like:
84 176 D40 546896
105 176 D40LF 1213781
39 177 D40 446819

The Count(Distinct is the main obstacle here. I can assemble something
that works, but unfortunately either groups dates together prior to
counting the OBJ_CODE, or fails to do any grouping at all resulting in
a count of '1' for each event.

Thanks to anyone who even dare reply to this thread now !!! I'll be
busy digging my grave.

 

[Lynn Trapp]

So is this an Oracle database you are trying to query? I don't know anything
about MS Query or Merant but am quite conversant in Oracle.

 

[S Davis]

Thanks, I've solved this in another thread with a better description of
what was needed. I'll post the link below for future generations of
frustrated squealers (SQLrs). Many thanks,
-Sean

http://groups.google.ca/group/micro...s/browse_thread/thread/915c015c80ef9562?hl=en