| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2002-01-21 : 21:50:25
|
| hi small clarifying about having clause i refered but not clear... with having and where....my query areselect count(distinct Account_ID) from bill_process having sum(Qty_usg/60)>1250result :-- 10025select count(distinct account_id) from bill_process where account_id in(select distinct Account_ID from bill_process agroup by Account_ID having sum(Qty_usg/60)>1250)result :-- 2142 i read something which says that having restrict the row return by the query but does not affect the calculation of the aggregate what does that mean with regardskhalik |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-21 : 22:03:37
|
| The where clause will restrict the number of rows per group.The having clause will then work on the aggreagte function for those rows in the group to decide whether or not to return the group.Your first query has no group so the having clause will calculate sum(Qty_usg/60)>1250 on all rows - it will then either return count(distinct Account_ID) for the whole table or return 0.Your second query has split the result into groups on account_id.It will then only return the count of Account_ID's for which sum(Qty_usg/60)>1250).==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-21 : 22:08:33
|
| thanks nr,by the result i am able to understant that the first query is count of all the rows (distinct account_id)... then what is the role of having clause.... i read that if having used with out group it acts like where...will it not work like..select count(distinct account_id ) from bill process where sum(Qty_usg/60)>1250i need a query which will the desire result with out subquery.. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-21 : 22:28:54
|
| Have you tried it?select count(distinct account_id) from bill processwhere sum(Qty_usg/60)>1250should give an error.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-21 : 22:35:29
|
| ya.. we cannot have a where with a aggregate funcation... i knowwhat i want to know isA) select count(distinct account_id ) from bill process having sum(Qty_usg/60)>1250B) select count(distinct account_id ) from bill process where sum(Qty_usg/60)>1250i know a return a error but something like that...is A and B mean the same logically....how do i get the desire result which should have the count of distinct account_id whose usage is > 1250 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-21 : 22:35:37
|
| Your requirement seems to be for a count of all the Account_ids which satisfy the aggregate.To do the aggregate test you will need to use a query which segregates accounts, the count will have to work accross those accounts so you will need something like a subquery.select count(*)from(select Account_ID from bill_processgroup by Account_ID having sum(Qty_usg/60)>1250)) as aA derived table - not much different from a subquery.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-01-21 : 22:38:24
|
| nrI'm confused but - no, it doesn't give an error - it just returns the number of records in the table, with the Having clause applied to the whole table as if it was a group - ie if sum(Qty_usg/60)>1250 for all records in the table, then it will just return the number of records in the table.Edited by - rrb on 01/21/2002 22:40:18 |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-21 : 22:49:42
|
| derived table or subquery return the same result but the performances can't i get in a simple query... if no... what does it mean byhaving used with out group it acts like where... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-21 : 22:50:41
|
| I was indicating the aggregate in the where clause which should give an error.Without a group by the having clause will aggregate the whole table. The select will just return one value as it is a single group.The having and where will do the same thing.As the where cannot take aggregates putting the same code in the having will just restrict the rows.With a group by clause the having can only accept the group fields without an aggregate and putting the filter in the whare clause will have the same effect.Basically if you can put the code in a where clause you should.In your case it can't be done easily (I think) without a subquery or similar).==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-21 : 23:12:48
|
| ok thanks for u help and info...conclution isits not possible to get the result without subquery or derived.. rightbyethanks a lotgreat job |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-21 : 23:19:21
|
| Think it might be possible with a self join.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-01-21 : 23:21:53
|
| Sorry nigel - yip you're right as always - So khalik - why no subqueries? - If your problem is that you're doing it ADO - then create a view for your subquery ...?I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-21 : 23:28:24
|
| Nope don't believe it's possible in a single query.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-21 : 23:29:44
|
| i have a huge base and i want the result with simple query no subquery it will slow a bit and i cannot create index on this table i have several on it.. so i felt may be some other option... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-21 : 23:37:02
|
| The query to get all the accounts isselect Account_ID from bill_processgroup by Account_ID having sum(Qty_usg/60)>1250)And you will have to do all this processing to find out the accounts to count.If as you say the count of these is about 2142 then having a subquery will not be slowing it down much - not until you get to several tens of thousands of accounts will it have much impact - but then the query above will be taking longer too.I would assume you already have an index on Account_ID - you could make this compound with Qty_usg to speed it up a bit.The only way I can see to have much impact would be to hold the sum in an agregate table and either update it when the main table is updated or treat this as a batch report and do the updates overnight.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-21 : 23:59:42
|
| the result 2142 is only for few days and i have for 3 month data.. and the actual base is very big... the query select Account_ID from bill_process group by Account_ID having sum(Qty_usg/60)>1250) will give all the account but i need a single row with the count.. no problem i think when i am spending 10 hours in loading data and processing few minutes is ok....i will compare the derived table and selfjoin and check the performances which one is better....thanks for the help ...thanks a lotNo problem is Big than U |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-22 : 00:07:27
|
| Don't think you get the point.You will have to go through all the records to get the account aggregates soselect Account_ID from bill_processgroup by Account_ID having sum(Qty_usg/60)>1250)is necessary.For the server to keep a record of the account IDs and count them afterwards should not give much overhead i.e. the fact that this query is a subquery should not affect the time taken by much. Whatever you do to the query it will still have to do this processing.The only way you can speed this up is by pre-calculating the aggregates.You could keep a record of the last time an account is updated, keep a record of the aggregate from the query and exclude those accounts which have not been updated since the last time it was run.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-22 : 00:25:47
|
| ok got the point thanks byeAsk to your self before u ask someone |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2002-02-19 : 14:20:14
|
| Here is how a SELECT works in SQL ... at least in theory. Real products will optimize things when they can. a) Start in the FROM clause and build a working table from all of the joins, unions, intersections, and whatever other table constructors are there. The table expression> AS <correlation name> option allows you give a name to this working table which you then have to use for the rest of the containing query. b) Go to the WHERE clause and remove rows that do not pass criteria; that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE clause is applied to the working in the FROM clause. c) Go to the optional GROUP BY clause, make groups and reduce each group to a single row, replacing the original working table with the new grouped table. The rows of a grouped table must be group characteristics: (1) a grouping column (2) a statistic about the group (i.e. aggregate functions) (3) a function or (4) an expression made up of the those three items. d) Go to the optional HAVING clause and apply it against the grouped working table; if there was no GROUP BY clause, treat the entire table as one group. e) Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The AS operator can give a name to expressions in the SELECT list, too. These new names come into existence all at once, but after the WHERE clause, GROUP BY clause and HAVING clause has been executed; you cannot use them in the SELECT list or the WHERE clause for that reason. If there is a SELECT DISTINCT, then redundant duplicate rows are removed. For purposes of defining a duplicate row, NULLs are treated as matching (just like in the GROUP BY). f) Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained. --CELKO--Joe Celko, SQL Guru |
|
|
|
|
Having Clause