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
  

Poston Sun, 20 Jan 2008 6:16 pm

Good.

How can you make a query without being by views, for example to say which the customer with greater number of fines?
Or the customer who bought another product XYZ?

Is that whenever I try to apply a count and a max on query always gives me error.


Thank you
SepulRise
Location: Portugal

Poston Sun, 20 Jan 2008 6:29 pm

Code: Select all
select a . name, c . nome_produto ( b QUANTIDADE_PRODUTO ., count ) the quantities from customer a, linha_compra b, c, d purchase product where c = nome_produto . ' flour ' and product_id = b . c . and b id_compra . product_id = d . id_compra and the . customer_id = d . group by Customer_id the . name, c . nome_produto order by Quantity desc;
I forgot to put the query you had done.
SepulRise
Location: Portugal

Poston Mon, 21 Jan 2008 7:12 am

Brother, Put us here the error is occurring.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Mon, 21 Jan 2008 8:35 am

What is the error Thu returns when you run this select??
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Mon, 21 Jan 2008 12:04 pm

Sorry I explained myself badly.

The query gives no error, but I wanted to return was the maximum that results of this query. And I can't do that.

Thanks and I apologize for not making myself clear.
SepulRise
Location: Portugal

Poston Mon, 21 Jan 2008 12:09 pm

Brother, cool?

Make a test with this example below:
Code: Select all
SELECT MAX(b.QUANTIDADE_PRODUTO)
  FROM produto b;
anything, send it to us here, beleza?
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Mon, 21 Jan 2008 12:23 pm

But it is possible to do this in the same query that I indicated above?
SepulRise
Location: Portugal

Poston Mon, 21 Jan 2008 12:42 pm

Brother, Makes 1 ai test (I'm not your modeling if der some error, returns here):
Code: 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;
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Mon, 21 Jan 2008 12:54 pm

The code works flawlessly. Thanks A Lot.

But I have a small doubt. It is possible that last query, to return only the first result?

She returns several maximums per person, but how do I return the greatest maximum?


Thank you
SepulRise
Location: Portugal

Poston Mon, 21 Jan 2008 1:17 pm

Brother, This per person maximum, is why no people has the same maximum value?

If so, bring what? Any one of them?
Has how to post the result of the query (if not too large) here?
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Mon, 21 Jan 2008 1:31 pm

Ok I will post the result of the query.

[url = ://img80 http . imageshack us/. my . php? image = image2fx9 . jpg][img]http%20://img80%20.%20imageshack%20us/.%20img80/8574/image2fx9%20.%20th%20.%20jpg[/img] [/url]
SepulRise
Location: Portugal

Poston Mon, 21 Jan 2008 1:35 pm

Now in this listing you can see I just keria select the first result. IE select the maximum of maximums:)


Thank you
SepulRise
Location: Portugal

Poston Mon, 21 Jan 2008 1:49 pm

Brother, I couldn't see the image.
Is e-mail?
I will send my email in your mail box here on the Forum.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Mon, 21 Jan 2008 6:20 pm

From what I understand, you want only the first line of the query return
Code: 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 so, you could do the following:
Code: 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 above case, 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:
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
)
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Mon, 21 Jan 2008 8:36 pm

The code
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
)
give me the following error:) where seq = 1 * error on line 14: ORA-00904: \"SEQ\": invalid identifier
SepulRise
Location: Portugal

Poston Tue, 22 Jan 2008 7:40 am

And ai SepulRise, beauty?

in this query, you need to do 2 adjustments, one of which is for the name that you gave to column where you use the count, in this case you gave the name \"Quantities\", but at the point where you use the Rank you uses the name \"quantity\".
The second point is the location where you placed the seq, you let him along with the select rank, but in this case you need to put it in where the level higher..
Here is the select ready. try to rotate it, qualquer thing is just talk.

Code: 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
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Tue, 22 Jan 2008 9:11 am

Beauty.

This last query worked. Thanks man.

Thanks for the help ppl.

Hugs ...
SepulRise
Location: Portugal

Poston Tue, 22 Jan 2008 5:37 pm

this query you need to do 2 adjustments, one of which is for the name that you gave to where you use the column count, in this case you gave the name \"Quantities\", but at the point where you use the Rank you uses the name \"quantity\".
The second point is the location where you placed the seq, you let him along with the select rank, but in this case you need to put it in where the level higher..
Here is the select ready. try to rotate it, anything you name it.


Ops ... crashes my ...

Thanks for correcting the query, Tineks.[/quote]
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Wed, 23 Jan 2008 7:23 am

Oops, quiet Rafael!!
something or other the times passes without agent understand .... lol ...

Hugs!!!!
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests