views:

63

answers:

6

Hello. I'm trying to make a query to retrieve the region which got the most sales for sweet products. 'grupo_produto' is the product type, and 'regiao' is the region. So I got this query:

SELECT TOP 1 r.nm_regiao,  (SELECT COUNT(*)
        FROM Dw_Empresa
        WHERE grupo_produto='1' AND 
        cod_regiao = d.cod_regiao) as total 
FROM Dw_Empresa d
INNER JOIN tb_regiao r ON r.cod_regiao = d.cod_regiao ORDER BY total DESC

Then when i run the query, MS-Access asks for the "total" parameter. Why it doesn't consider the newly created 'column' I made in the select clause?

Thanks in advance!

A: 

Try using a subquery and order the results in an outer query.

SELECT TOP 1 * FROM
(
    SELECT
        r.nm_regiao, 
        (SELECT COUNT(*)
         FROM Dw_Empresa
         WHERE grupo_produto='1' AND cod_regiao = d.cod_regiao) as total 
    FROM Dw_Empresa d
    INNER JOIN tb_regiao r ON r.cod_regiao = d.cod_regiao
) T1
ORDER BY total DESC

(Not tested.)

Mark Byers
It's weird. When I try that, the "total" column becomes all 0 and instead of one row showing up, I get 10 similar rows. I'm trying to figure out why. That's the way, though. Thanks
ConradoClark
A: 

Aliases are only usable in the query output. You can't use them in other parts of the query. Unfortunately, you'll have to copy and paste the entire subquery to make it work.

VoteyDisciple
You can't use the alias, but you can repeat the expression being aliased. Jet will calculate it only once, so it's only inefficient in regard to typing.
David-W-Fenton
Not in this case. You will get an error if you try to order by the posted subquery.
Remou
"Aliases are only useful in the query output" -- I disagree: I think it is very useful to be able to use 'aliases' in the `ORDER BY` clause.
onedaywhen
"You can't use ['aliases'] in other parts of the query" -- that's because those 'other parts' are resolved before the `SELECT` clause. However, the `ORDER BY` clause is applied after the `SELECT` clause so it shouldn't be a problem... it *shouldn't* be but in Access it is :(
onedaywhen
Aliases are ***useful*** throughout the entire query, including other fields in the `SELECT` list. I should have said "usable." I will edit to clarify.
VoteyDisciple
I haven't delved into the SQL enough to tell, but I'm not certain it requires a subquery. Seems to me it might be doable with a JOIN on a derived table (i.e., in the FROM clause). But as I say, I'm not looking that closely at the SQL.
David-W-Fenton
A: 

Assuming the ORDER BY clause is the source of the problem, you could reference your total field by its ordinal position rather than (alias) name:

SELECT TOP 1 r.nm_regiao,  (SELECT COUNT(*)
        FROM Dw_Empresa
        WHERE grupo_produto='1' AND 
        cod_regiao = d.cod_regiao) as total 
FROM Dw_Empresa d
INNER JOIN tb_regiao r ON r.cod_regiao = d.cod_regiao
ORDER BY 2 DESC
HansUp
Doesn't seem to work as well. Whenever I put "order by" with "top 1" I get zeroes in the 'total' column. Really weird
ConradoClark
Are you saying the query returns the correct results without an ORDER BY clause? I'm mystified. Please consider including sample rows from your DW_empresa and tb_regiao tables.
HansUp
A: 

How about:

SELECT TOP 1  r.nm_regiao 
FROM (SELECT Dw_Empresa.cod_regiao, 
             Count(Dw_Empresa.cod_regiao) AS CountOfcod_regiao
      FROM Dw_Empresa
      WHERE Dw_Empresa.[grupo_produto]='1'
      GROUP BY Dw_Empresa.cod_regiao
      ORDER BY Count(Dw_Empresa.cod_regiao) DESC) d
INNER JOIN tb_regiao AS r 
ON d.cod_regiao = r.cod_regiao
Remou
A: 

Why it doesn't consider the newly created 'column' I made in the select clause?

Because Access (ACE/Jet) is not compliant with the SQL-92 Standard.

Consider this example, which is valid SQL-92:

SELECT a AS x, c - b AS y
  FROM MyTable
 ORDER
    BY x, y;

In fact, anything other than x and y is invalid in the ORDER BY clause because everything else is out of scope.

However, Access chokes on the above syntax. The equivalent Access syntax is this:

SELECT a AS x, c - b AS y
  FROM MyTable
 ORDER
    BY a, c - b;

However, I understand from @Remou's comments that a sunquery in the ORDER BY clause is invalid in Access.

onedaywhen
A: 

I suggest using an intermediate query.

 SELECT r.nm_regiao, d.grupo_produto, COUNT(*) AS total
   FROM Dw_Empresa d INNER JOIN tb_regiao r ON r.cod_regiao = d.cod_regiao
   GROUP BY r.nm_regiao, d.grupo_produto;

If you call that GroupTotalsByRegion, you can then do:

SELECT TOP 1 nm_regiao, total FROM GroupTotalsByRegion 
  WHERE grupo_produto = '1' ORDER BY total DESC

You may think it's extra work to create the intermediate query (and, in a sense, it is), but you will also find that many of your other queries will be based off of GroupTotalsByRegion. You want to avoid repeating that logic in many other queries. By keeping it in one view, you provide a simplified route to answering many other questions.

Larry Lustig
You don't have to save it -- you should be able to use a derived table in the FROM clause in place of the saved QueryDef.
David-W-Fenton