I am converting a database from MS-Access to MySQL (4.0.20).
In MS-Access I had a query as part of the database (normal SQL term is "view" I have learned) as follows:
SELECT Date, A, B, MAX(V) AS MaxV
FROM Table1
GROUP BY Date, A, B
Lets call that query "Query1". Then I had another query (aka: view):
SELECT Date, SUM(MaxV) As TotalV
FROM Query1
GROUP BY Date
We'll call that "Query2". Then I could have my program do the following:
SELECT *
FROM Query2
WHERE Date=MyDate
Since views are not supported in MySQL (until 5.0 is done) and nested select statments are only supported on MySQL 4.1 (which my hosting service has yet to install), then the only way I can think of to accomplish this task is to CREATE a temp table, SELECT INTO, then query it, and then DROP it when done. This is lame! Can anyone come up with a better way?
To clarify, this is what I would do from the query in my program if nested SELECT statments were aloud:
SELECT Date, SUM(MaxV) As TotalV
FROM (
SELECT Date, A, B, MAX(V) As MaxV
FROM Table1
GROUP BY Date, A, B)
WHERE Date=MyDate
GROUP BY A
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Um
Perhaps I am missing something:
Query 1:
SELECT Date, A, B, MAX(V) AS MaxV
FROM Table1
GROUP BY Date, A, B
Then Query 2:
SELECT Date, SUM(MaxV) As TotalV
FROM Query1
GROUP BY Date
Looks like you can replace Query2 with:
SELECT Date, SUM(MaxV) As TotalV
FROM Table1
GROUP BY Date
since none of the aggregates used in Query1 make an appearance in query 2...?
HTH
Cameron
? You are missing something...
MaxV doesn't exist in Table 1. It is created by finding the Mav V amongst rows that have the same Date, A, B, but different C's.
The Table1 primary index is Date, A, B, C. I need to find the Max V within each group of rows containing the same Date, A, B, and then sum the Max V's of the result for each group of rows containing the same Date.
Example:
Table 1
--------
Date A B C V
1/1/05 1 1 1 5
1/1/05 1 1 2 6
1/1/05 1 2 1 3
1/1/05 1 2 2 8
So query 1 would have
1/1/05 1 1 MaxV=6
1/1/05 1 2 MaxV=8
and query 2 would have
1/1/05 TotalV=14
The only way I currently know how to do this in MySQL 4.0 is to create a temp table with Date, A, B, MaxV fields, then select into that table, then query the table for TotalV, and finally (after using the data) drop the temp table. That is totally lame ;). Using a "view" is sooo much better. My question is: Is there anyway to get the data I want without having to create and later drop a temp table?
No no, I am following you now - I was thrown off by the GROUP BY A in your example. What you really want is:
SELECT Date, SUM(MaxV) As TotalV
FROM (
SELECT Date, A, B, MAX(V) As MaxV
FROM Table1
GROUP BY Date, A, B)
GROUP BY Date
HAVING Date=MyDate
right?
If so, I think your "totally lame" approach of the temp table might be best...well, no - upgrading to 4.1 would be best ;^)
sorry...I'll keep thinking.
cameron
Business Accounts
Answer for Membership
by: akshah123Posted on 2004-07-28 at 08:26:56ID: 11657536
you will have to write a script that does that for you. Since your mysql does not handle subqueries it does not allow stored procedures. My Idea is that you should write a script in php.
The only way to accomlish your task with MySQL 4.0 is by some sort of php or other script. If you do not know php, i suggest that you acquire report running program called Crystal Report. This is similar to report generation in access but will you to write more complex report from number of different databases.