microsoft.public.sqlserver.server - Re: Does Anyone Know.....It Worked..Your example worked !!

Author

Re: Does Anyone Know.....It Worked..Your example worked !!

Gary

2002-10-26, 1:23 pm

It worked!! Your CASE example worked. Thank you Roy.
You've made my weekend. I was just hung up on this
sub-query thing, as it seemed the most logical way
to get counts of records, where various conditions
were met.

I just didn't think to use CASE for something like this,
where you just add up the "one-zees", whenever a specific
condition within a record was satisfied. I should have
realized it would work, but like I said...your brain
gets going one way, and.....

Using CASE in this manner doesn't seem as logical, but
it sure works. The table I'm running against,
contains 200 million records, with over 100 fields in
each record. Yet I was able to get a daily breakdown of
5 different statistics, over a 30 day time frame,
in less than 10 seconds.

So, I guess it's not as illogical or inefficient as I
thought. Anyway, you've made my weekend. I can't thank
you enough. MANY MANY THANKS !!!! I owe you one.

Gary
===============
Roy Harvey wrote:
> Gary,
>
> It is not particularly clear to me what you are expecting for output,
> but perhaps the following will give you some ideas you can work with:
>
> SELECT Day,
> SUM(CASE WHEN Day between @date1 and @date2
> AND (this and that)
> THEN 1 ELSE 0
> END) as Statistic1,
> SUM(CASE WHEN Day between @date1 and @date2
> AND (whatever)
> THEN 1 ELSE 0
> END) as Statistic2
> FROM MyTable
> GROUP BY Day
>
> Roy
>
> Gary <gary.nospam@earthlink.net> wrote:
>
>
>>I've written a query that uses several sub-queries
>>to gather different statistics from a table. The
>>sub-queries work fine, UNTIL I try to use GROUP-BY.
>>Then, I get an error message, indicating the sub-queries
>>cannot return more than 1 row value, or something like
>>that. This happens when I try to put a "GROUP BY Day"
>>statement in each sub-query.
>>
>>And by adding the "GROUP BY Day" at the end of the query,
>>only applies to the first SELECT variable which is DAY.
>>I thought a GROUP BY at the end, after the sub-queries,
>>would apply to each sub-query. But apparently,
>>it doesn't.
>>
>>There has to be a way. Below is an abbreviated,
>>simplified version of my QUERY. I just want to
>>"group by" the "day" variable, so I can get my
>>statistics by day. Maybe using sub-queries isn't
>>the way to go, for what I'm looking for?????
>>
>>SELECT Day,
>>
>>(SELECT Count(*)
>> FROM MyTable
>> WHERE (Day between #Date1# and #Date2#) AND
>> (this and this and that ) ) As Statistic1
>>
>>(SELECT Count(*)
>> FROM MyTable
>> WHERE (Day between #Date1# and #Date2#) AND
>> (this that and the other thing) ) as Statistic2
>>
>
>>FROM MyTable
>
>>WHERE (Day between #Date1# and #Date2#) AND
>> (this and that)
>>
>>GROUP BY Day
>>ORDER BY Day
>>
>>I only showed 2 sub-queries in this example, but I
>>actually have 5 sub-queries, to return 5 different
>>computed values (or statistics) that I need to gather
>
>>from my table. Again, it works fine, providing "totals
>
>>only" if I remove the GROUP BY statement.
>>
>>Thanks for any/all assistance. Gary.
>
>

Roy Harvey

2002-10-27, 8:23 pm

Gary,

>It worked!! Your CASE example worked. Thank you Roy.
>You've made my weekend. I was just hung up on this
>sub-query thing, as it seemed the most logical way
>to get counts of records, where various conditions
>were met.

Subqueries can work fine for this, but you would need:

1) A table of just the days, and

2) To make them correlated subqueries.

Imagine table D had just one column, day, and had rows matching the
entire range of days in MyTable:

SELECT T.Day,
(SELECT Count(*)
FROM MyTable
WHERE T.Day = D.Day
AND (this and this and that)
) As Statistic1,
(SELECT Count(*)
FROM MyTable
WHERE T.Day = D.Day
AND (whatever)
) As Statistic2,
.....
FROM MyTable as T

However, as each subquery might be implemented as yet another pass
against MyTable it is unlikely to perform better, and may well perform
visibly worse.

Roy

Gary

2002-10-31, 8:23 am

Thanks again, Roy. I really do owe you one for this
help. I can see how I can use your original CASE
example for many different queries, where I need
individual statistics and counts, with varying
conditions for each statistic.

However, it seems that the variable you are going to
GROUP BY, in my case, "DAY", needs to be listed in
the first SELECT statement.

Then, it seems I need to have a WHERE clause, that
includes all the common "WHERE" elements from all
the various CASE statements. If I leave the "global"
WHERE statement off, I get too many records.

For example, if each CASE statement is producing
COUNTS WHERE Form.Date1 <= DAY <= Form.Date2 ,
then, I need to have that same condition in the
"global" WHERE statement at the bottom of my
query, just above the GROUP BY DAY.

If I don't do this, the query tries to run every
day, for some reason. Does this sound normal?
Just curious. Again, many thanks.

Gary

p.s. Maybe I could get you a $10 certificate from
Amazon. Do you ever shop there? Just a small way to
show my appreciation for your help.
===============
Roy Harvey wrote:
> Gary,
>
>
>>It worked!! Your CASE example worked. Thank you Roy.
>>You've made my weekend. I was just hung up on this
>>sub-query thing, as it seemed the most logical way
>>to get counts of records, where various conditions
>>were met.
>
>
> Subqueries can work fine for this, but you would need:
>
> 1) A table of just the days, and
>
> 2) To make them correlated subqueries.
>
> Imagine table D had just one column, day, and had rows matching the
> entire range of days in MyTable:
>
> SELECT T.Day,
> (SELECT Count(*)
> FROM MyTable
> WHERE T.Day = D.Day
> AND (this and this and that)
> ) As Statistic1,
> (SELECT Count(*)
> FROM MyTable
> WHERE T.Day = D.Day
> AND (whatever)
> ) As Statistic2,
> .....
> FROM MyTable as T
>
> However, as each subquery might be implemented as yet another pass
> against MyTable it is unlikely to perform better, and may well perform
> visibly worse.
>
> Roy

Sponsored Links