Good.
How can you do a query without being by views, for example to tell you which customer with a higher number of fines?
or the customer who bought another XPTO product?
is that whenever I try to apply a count and a max in Query gives me an error.
Thanks
Help in applying Count and Max
select a.nome, c.nome_produto, count(b.QUANTIDADE_PRODUTO) as Quantidades
from cliente a, linha_compra b, produto c, compra d
where c.nome_produto= 'Farinha' and
c.id_produto = b.id_produto and
b.id_compra = d.id_compra and
a.id_cliente = d.ID_CLIENTE
group by a.nome, c.nome_produto
order by Quantidades desc;
-
- Moderador
- Posts: 2016
- Joined: Wed, 12 Jan 2005 3:25 pm
- Location: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Brother,
put us here the error that is occurring.
put us here the error that is occurring.
Sorry, I explained badly.
Query does not give a mistake, but I wanted to return was the maximum that results from that query. And I can not do that.
Thanks and I apologize for not explaining me well.
Query does not give a mistake, but I wanted to return was the maximum that results from that query. And I can not do that.
Thanks and I apologize for not explaining me well.
-
- Moderador
- Posts: 2016
- Joined: Wed, 12 Jan 2005 3:25 pm
- Location: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Brother, quiet?
does a test there, with this example below:
Anything, send here to us, beleza?
does a test there, with this example below:
SELECT MAX(b.QUANTIDADE_PRODUTO)
FROM produto b;
-
- Moderador
- Posts: 2016
- Joined: Wed, 12 Jan 2005 3:25 pm
- Location: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Brother,
Make 1 test there (I do not have your modeling .. If you give some errors, it returns here):
Make 1 test there (I do not have your modeling .. If you give some errors, it returns here):
SELECT a.nome,
c.nome_produto,
b.quantidade_produto AS quantidades
FROM cliente a,
linha_compra b,
produto c,
compra d
WHERE c.id_produto = b.id_produto
AND b.id_compra = d.id_compra
AND a.id_cliente = d.id_cliente
AND b.quantidade_produto = (SELECT MAX(quantidade_produto)
FROM produto
WHERE c.nome_produto = 'Farinha')
ORDER BY b.quantidade_produto DESC;
The code works perfectly. Thanks.
But I have another little doubt. Is it possible in this last query, do return only the first result?
It returns several maximum per person, but how do you return the larger maximum?
Thanks
But I have another little doubt. Is it possible in this last query, do return only the first result?
It returns several maximum per person, but how do you return the larger maximum?
Thanks
-
- Moderador
- Posts: 2016
- Joined: Wed, 12 Jan 2005 3:25 pm
- Location: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Brother,
This maximum per person , is why do not people have the same maximum amount?
Positive case, bring which? Anyone of them?
Can you post the result of Query (if not too big) here?
This maximum per person , is why do not people have the same maximum amount?
Positive case, bring which? Anyone of them?
Can you post the result of Query (if not too big) here?
-
- Moderador
- Posts: 2016
- Joined: Wed, 12 Jan 2005 3:25 pm
- Location: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Brother,
I could not view the image.
How can you send it by email?
I'll send my e-mail in your message box here from the forum.
I could not view the image.
How can you send it by email?
I'll send my e-mail in your message box here from the forum.
-
- Rank: DBA Pleno
- Posts: 232
- Joined: Fri, 30 Mar 2007 7:26 pm
- Location: Londrina - PR
Rafael O. Genaro
From what I understand, you just want the prieme return line of the query
, right?
If that's right, you could do the following:
Of course, in the case above, if there are two or more customers with The same maximum amount, only one of them will be returned.
To display all results, you could do:
select a.nome, c.nome_produto, count(b.QUANTIDADE_PRODUTO) as Quantidades
from cliente a, linha_compra b, produto c, compra d
where c.nome_produto= 'Farinha' and
c.id_produto = b.id_produto and
b.id_compra = d.id_compra and
a.id_cliente = d.ID_CLIENTE
group by a.nome, c.nome_produto
order by Quantidades desc;
If that's right, you could do the following:
select * from
(
select a.nome, c.nome_produto, count(b.QUANTIDADE_PRODUTO) as Quantidades
from cliente a, linha_compra b, produto c, compra d
where c.nome_produto= 'Farinha' and
c.id_produto = b.id_produto and
b.id_compra = d.id_compra and
a.id_cliente = d.ID_CLIENTE
group by a.nome, c.nome_produto
order by Quantidades desc
) where rownum < 2
Of course, in the case above, if there are two or more customers with The same maximum amount, only one of them will be returned.
To display all results, you could do:
select nome, nome_produto, quantidade
from
(
select nome, nome_produto, quantidade, rank() over (order by quantidade desc) seq
from
(
select a.nome, c.nome_produto, count(b.QUANTIDADE_PRODUTO) as Quantidades
from cliente a, linha_compra b, produto c, compra d
where c.nome_produto= 'Farinha' and
c.id_produto = b.id_produto and
b.id_compra = d.id_compra and
a.id_cliente = d.ID_CLIENTE
group by a.nome, c.nome_produto
) where seq = 1
)
In the code
of me the following error WHERE SEQ = 1
*
Error in line 14:
ORA-00904: "SEQ": invalid identifier
select nome, nome_produto, quantidade
from
(
select nome, nome_produto, quantidade, rank() over (order by quantidade desc) seq
from
(
select a.nome, c.nome_produto, count(b.QUANTIDADE_PRODUTO) as Quantidades
from cliente a, linha_compra b, produto c, compra d
where c.nome_produto= 'Farinha' and
c.id_produto = b.id_produto and
b.id_compra = d.id_compra and
a.id_cliente = d.ID_CLIENTE
group by a.nome, c.nome_produto
) where seq = 1
)
*
Error in line 14:
ORA-00904: "SEQ": invalid identifier
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
And then sepulrise, beauty ??
In this query you need to make 2 adjustments, one of them is referring to the name you gave to the column where you use the count, in this case you gave the name "quantities", but at the point where you Uses rank you use the name "quantity".
The second point is the place where you put the SEQ, you left it along with the select of the rank, but in this case you need to put it in the web of the level above ..
Here is the select Ready .. Try to run it, whatever it is just to speak.
[] 's
In this query you need to make 2 adjustments, one of them is referring to the name you gave to the column where you use the count, in this case you gave the name "quantities", but at the point where you Uses rank you use the name "quantity".
The second point is the place where you put the SEQ, you left it along with the select of the rank, but in this case you need to put it in the web of the level above ..
Here is the select Ready .. Try to run it, whatever it is just to speak.
SELECT nome, nome_produto, quantidade
FROM (SELECT nome, nome_produto, quantidade,
RANK () OVER (ORDER BY quantidades DESC) seq
FROM (SELECT a.nome, c.nome_produto,
COUNT (b.quantidade_produto) AS quantidades
FROM cliente a, linha_compra b, produto c, compra d
WHERE c.nome_produto = 'Farinha'
AND c.id_produto = b.id_produto
AND b.id_compra = d.id_compra
AND a.id_cliente = d.id_cliente
GROUP BY a.nome, c.nome_produto
)
)
WHERE seq = 1
-
- Rank: DBA Pleno
- Posts: 232
- Joined: Fri, 30 Mar 2007 7:26 pm
- Location: Londrina - PR
Rafael O. Genaro
Ops .. My fails ...In this query you need to make 2 adjustments, one of them is referring to the name you gave to the column where you use the count, in this case you gave the name "amounts", but at the point where you use the rank you use the name "The amount".
The second point is the place where you put the SEQ, you left it along with the SELECT of the rank, but in this case you need to put it on the level of the level ..
Here is the Select Ready .. Try to run it, anything is just speaking.
Thanks for correcting Query Tineks. [/quote]
-
- Information
-
Who is online
Users browsing this forum: No registered users and 18 guests