sql query on a query

Hi, I'm using tAdoDataSet to query data from my sql server.   It's working fine but now I wan't to do something more complex and I need to query the results like access would do.

For exemple, I'm compiling all invoices in a query.    Next I wan't to sum for each customer the total amount invoiced (from my query result).

I could probably do this in one query but I need to make some complex filtering on things like groups, category and prices and I have found that it's really much slower using a where condition vs a having condition (which I must use if I'm grouping to create a sum).
Buropro-CitationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
As it's SQL Server: The fastest results are achieved by using stored procedures.
Sinisa VukSoftware architectCommented:
like ste5an said - use procedures... I use them a lot.... Half of my business logic I put in procedures ....
Allows very complex filtering, sorting, grouping... Sometimes I create temporary table in procedure and do very complex calculations.
jimyXCommented:
What is your question please?

I will guess it's about:
where condition vs a having condition

"WHERE" works on each row to filter and to build the result set.
"HAVING" works to filter the result set after it was built, and it works mostly with aggregation (Max, Min, Avg, ...etc).

Every Query has it's own way to use WHERE & HAVING.
So keep in mind HAVING is not synonymous for WHERE to match which is faster. They both work together towards achieving your goal.

For instance, consider this example that shows the difference:
"TableX" is a Table that has one column "Col1" of the type Integer, and has the values (1, 2, 3, 4, 9, 10).
If you want to get the Min value, but only for values greater than three, then your SQL looks like:
Select min(Col1)
from TableX
where Col1 > 3;

Which should get you 4.
What happens is, that all rows with values less than or equal to three are excluded from the evaluation then the Min value is returned out of the selection of rows as result set.

On the other hand, to do the same with HAVING you need to do this:
select Min(Col1) from
(Select Col1
from Tablex
having Col1 > 3) as T;

Which will get you 4 as well.
But all values greater than three are returned first, and then the Min value is determined out of the result set.

Hope this clears the difference.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Buropro-CitationAuthor Commented:
I'm not familiar with stored procedures, how does it work?

jimyX: Basically, my question was how can I work from an adodataset result to query again on it so I can group some data.    The speed problem I'm experiencing may not be due to the "having" part, maybe it's simply because I'm grouping data and it's more data to process, this has made me a aware of the speed problem I'm getting on some table and query on a query may help me get around this instead of one big complex query.

thank you for all your help, it's appreciated.
ste5anSenior DeveloperCommented:
Well, please post a concise and complete example. Otherwise we can only guess what your doing..
jimyXCommented:
I see.

MySQL reference explains how to deal with Stored Procedures, but writing a correct/simpler query will not be fixed by SP.

Probably you can post your query with some details and you might get simpler query rewritten.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter RitzelSenior Software EngineerCommented:
So, since no solution was determined so far, I'm jumping in.
Let's clarify some things:
1) A single query to solve that would be much more faster than using adoDataSet any time. Because the query will run straight on database, with no network to cause slowness;
2) If you state your problem clear, we can decide if your filtering needs are really complex or not;
3) You are not required to use having because you have used group by in your query;
4) Performance could be harmed because you lack indexes on your table for the filter you are using.

Please define your problem again, with examples and any attachments, and I'm pretty sure that you are going to have a satisfactory answer.
Buropro-CitationAuthor Commented:
I have found some answer so far, the with command does exactly what I want.   Here is a simplified example:

with t1 as (select * from invoice where (CONVERT(VARCHAR(10),date, 121) ='2014-03-24'))
SELECT top 10 succ, customer, Sum(mnt_invoice) AS SommeTotal from t1
where succ=1 GROUP BY date,succ, customer
order by succ, sommetotal desc;

I'm not sure if it's the fastest way to do this,  I'll take a look in store procedure, it seems an interesting solution also.
PortletPaulEE Topic AdvisorCommented:
For performance; this you should NOT do:

    WHERE (CONVERT(varchar(10), date, 121) = '2014-03-24')

Do NOT run functions on every row of data to suit a single value.
Instead, change the value(s) to suit the data!

    WHERE [date] >= '20140324' and [date] < '20140325'

or

    WHERE [date] >= @datevar and [date] < dateadd(day,1,@datevar)

---------------

& you do not need a CTE for this, a simple nested query would do it
Buropro-CitationAuthor Commented:
Thank you Paul, date format has always been a problem since I have some customers that have a date format that is not yyyymmdd, your solution seem to work around this problem quite nicely, I'll be sure to use it from now on :)
Geert GOracle dbaCommented:
did you try group by rollup ?
transact-sql :
https://technet.microsoft.com/en-US/library/ms189305%28v=sql.90%29.aspx

select customer, sum(amount)
from invoices
group by customer with rollup

this will give null for the total
if you want the word "total":

select case when grouping(customer) = 1 then 'TOTAL' else customer end, sum(amount)
from invoices
group by customer with rollup
Buropro-CitationAuthor Commented:
I have found an alternative on the internet in the meantime.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.