Help in applying Count and Max

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

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
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

Select all

 
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; 
I forgot to get the query you had done.
Trevisolli
Moderador
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

Brother,

put us here the error that is occurring.
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 264
Joined: Sun, 19 Aug 2007 8:18 pm
Location: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

What is the error qui returns when you perform this select ??
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

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.
Trevisolli
Moderador
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

Brother, quiet?

does a test there, with this example below:

Select all

 
SELECT MAX(b.QUANTIDADE_PRODUTO) 
  FROM produto b; 
Anything, send here to us, beleza?
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

But is it possible to do this in the same query that I indicated on top?
Trevisolli
Moderador
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

Brother,

Make 1 test there (I do not have your modeling .. If you give some errors, it returns here):

Select all

 
 
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; 
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

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
Trevisolli
Moderador
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

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?
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

Ok I'll post the result of Query.

[url=http://img80.imageshack.us/my.php?image=image2fx9.jpg]Image
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

Now in this list that you can see I KERIA only select the first result. Or select the maximum of the maxims :)


Thanks
Trevisolli
Moderador
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

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.
rogenaro
Rank: DBA Pleno
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

Select all

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;
, right?

If that's right, you could do the following:

Select all

 
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 all

 
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 
) 
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

In the code

Select all

 
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  
)  
of me the following error :) WHERE SEQ = 1
*

Error in line 14:
ORA-00904: "SEQ": invalid identifier
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
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.

Select all

 
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 
[] 's
SepulRise
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Sun, 20 Jan 2008 6:11 pm
Location: Portugal

Beauty.

This last query worked. Thanks dude.

Thanks for PPL Help.

Hugs ...
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Fri, 30 Mar 2007 7:26 pm
Location: Londrina - PR
Rafael O. Genaro

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.
Ops .. My fails ...

Thanks for correcting Query Tineks. [/quote]
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Oops, tranquil Rafael !!
One thing or another sometimes passes without an agent to realize .... rsrs ..

Hugs !!!!
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 18 guests