pmelkebeke
asked on
Simplifying my select statement
I have a query defined which needs to calculate averages for differtent parts of a questionnaire. There are now 1000 records in for testing, but the real db contains over 40000 already.
My sql statement now takes like minutes to load for only those 1000 records.
Does anyone have any tips on simplifying the sql statement.
QuestionnaireOffline contains the results from the query with also CodePostal, the table CodePostal contains the province. I need to get overview of the responses per province.
My sql statement now takes like minutes to load for only those 1000 records.
Does anyone have any tips on simplifying the sql statement.
QuestionnaireOffline contains the results from the query with also CodePostal, the table CodePostal contains the province. I need to get overview of the responses per province.
SELECT DISTINCT (SELECT Count(*) FROM QuestionnaireOffline WHERE CodePostal IN (SELECT CP FROM CodesPostal WHERE Province='BRABANT WALLON')) AS nTotal, (SELECT count(*) FROM QuestionnaireOffline WHERE mode = "Papier" AND CodePostal IN (SELECT CP FROM CodesPostal WHERE Province='BRABANT WALLON')) AS nPapier, (SELECT count(*) FROM QuestionnaireOffline WHERE mode = "Internet" AND CodePostal IN (SELECT CP FROM CodesPostal WHERE Province='BRABANT WALLON')) AS nInternet, ...
FROM QuestionnaireOffline;
ASKER
I'm missing some kind of a DISTINCT in this picture. The total is way too high.
I have 5 different provinces. If i ran this query on my data then the total amount is way higher than the no of records I have.
I must say, If i sum all the results I get the same number of records as I have when I query (without DISTINCT) in a wrongly way like this:
I have 5 different provinces. If i ran this query on my data then the total amount is way higher than the no of records I have.
I must say, If i sum all the results I get the same number of records as I have when I query (without DISTINCT) in a wrongly way like this:
SELECT * FROM QUESTIONNAIREOFFLINE, Codespostal WHERE Codepostal = CP
pmelkebeke,
It would be useful to see some sample data. EE now allows you to directly upload
files to your question.
Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it. Therefore, be very careful about
posting proprietary, confidential, or other sensitive information. If necessary, use "fake"
and/or obfuscated data in your sample.
Regards,
Patrick
It would be useful to see some sample data. EE now allows you to directly upload
files to your question.
Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it. Therefore, be very careful about
posting proprietary, confidential, or other sensitive information. If necessary, use "fake"
and/or obfuscated data in your sample.
Regards,
Patrick
This query
SELECT * FROM QUESTIONNAIREOFFLINE, Codespostal WHERE Codepostal = CP
may not work as you expect. You will get a 'CARTESIAN JOIN' between the two tables. It should read:
SELECT QUESTIONNAIREOFFLINE.* FROM QUESTIONNAIREOFFLINE JOIN Codespostal on QUESTIONNAIREOFFLINE.CodeP ostal = Codespostal.CP
SELECT * FROM QUESTIONNAIREOFFLINE, Codespostal WHERE Codepostal = CP
may not work as you expect. You will get a 'CARTESIAN JOIN' between the two tables. It should read:
SELECT QUESTIONNAIREOFFLINE.* FROM QUESTIONNAIREOFFLINE JOIN Codespostal on QUESTIONNAIREOFFLINE.CodeP
ASKER
Okay, here it is.
EE-demodata.mdb
EE-demodata.mdb
ASKER
Arthur,
I know this. That's why I posted it. The result I get with the first proposal gives the same results as this cartesian join result...
I know this. That's why I posted it. The result I get with the first proposal gives the same results as this cartesian join result...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for this solution. I saw indeed that there were more results for some postal codes and I fixed it by using a query which filters out the doubles and using that one in the solution query. Thank you very much for the solution.
Sum(IIf(q.mode = "Internet", 1, 0) AS nInternet, ...
FROM QuestionnaireOffline q INNER JOIN
CodesPostal c ON q.CodePostal = c.CP
WHERE c.Province = "BRABANT WALLON"