Error in SQL. Different values when using count

Question

This is my table

+-----+-----+-----+-------+-------+---------+
| pa  |  pl |  rp |  corp |  year |  rating |
|-----+-----+-----+-------+-------+---------|
| pa1 | pl1 | rp1 |  a1   | 2016  |    6    |
| pa1 | pl1 | rp1 |  a1   | 2017  |    7    |
| pa1 | pl1 | rp1 |  a2   | 2016  |   6.5   |
| pa1 | pl1 | rp1 |  a2   | 2017  |   7.5   |
| pa1 | pl1 | rp2 |  a1   | 2016  |    2    |
| pa1 | pl1 | rp2 |  a1   | 2017  |   1.5   |
| pa1 | pl1 | rp2 |  a2   | 2016  |    4    |
| pa1 | pl1 | rp2 |  a2   | 2017  |   4.5   |
+-------------------------------------------+

Its name is list.

The query I wrote is to find where the average values have increased with time.

This is my query.

select count(sq.rp)
from
    (select l1.pa, l1.pl, l1.rp, l1.corp, l1.rating as r1, l2.rating as r2
     from list l1, list l2
     where l1.year = '2016' and l2.year = '2017' and l1.pa = l2.pa
       and l1.pl = l2.pl and l1.rp = l2.rp and l1.corp = l2.corp) sq
group by pa, pl, rp
having (avg(sq.r2)-avg(sq.r1)) > 0

When I don't use the count in the first line ( or replacing the first line with this line

select sq.rp

It shows the output result as with one row 'rp1'.

+-----+
| rp  |
|-----|
| rp1 |
+-----+

But when I use the 'count' keyword also, it shows the count as 2. I can't understand the reason.

Please switch to ANSI standard JOIN syntax, it's so much easier to read than comma separated joins. — HoneyBadger
– HoneyBadger, Commented Jan 2, 2018 at 10:27

pOrinG · Accepted Answer · 2018-01-02 10:54:41Z

3

Consider below example's

with x as
(select 'a' idx, 10 from dual union all
select 'a' idx, 20 from dual)
select idx from x group by idx

In the first you selected idx & group by idx but we havn't provided any aggregate functions but we are still grouping by. As per the document

In each group, no two rows have the same value for the grouping column or columns

so it actually just does distinct of column idx for you.

Try below with group by & count together and you might understand the query:

with x as
(select 'a' idx, 10 from dual union all
select 'a' idx, 20 from dual)
select idx,count(idx) from x group by idx

answered Jan 2, 2018 at 10:54

pOrinG

9454 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cee McSharpface · Accepted Answer · 2018-01-02 10:44:28Z

3

GROUP BY without an aggregate function is the same as using DISTINCT, therefore just one row. GROUP BY in combination with an aggregate function applies that aggregate function on all rows of the group, therefore 2. Execute the inner select without the group and you will see that it returns two rows because of the join.

answered Jan 2, 2018 at 10:44

Cee McSharpface

8,7133 gold badges43 silver badges90 bronze badges

Comments

Mauri · Accepted Answer · 2018-01-02 11:34:20Z

Not clear what data as result you need to obtain but to me is more readable join table with AVG already calculated and then just select the column you're interested in.. try the below example:

the table "list" should fit your example replacing columns pa pl and rp with k (key)

select * --sub2.k, sub2.av
from (
    select k,y, AVG(v) av
    from (
         select 'val1' k, 'a1' corp,'2016' y ,6 v
        union select 'val1', 'a2','2016' ,4 
        union select 'val1', 'a1','2017',7
        union select 'val1', 'a2','2017',9
        union select 'val2', 'a1','2016',1
        union select 'val2', 'a1','2017',3
        union select 'val2', 'a2','2016',3
        union select 'val2', 'a2','2017',5
    ) list
    where list.y = 2016
    group by k,y
    ) sub1
    join (
    select k,y, AVG(v) av
    from (
         select 'val1' k, 'a1' corp,'2016' y ,6 v
        union select 'val1', 'a2','2016' ,4 
        union select 'val1', 'a1','2017',7
        union select 'val1', 'a2','2017',9
        union select 'val2', 'a1','2016',1
        union select 'val2', 'a1','2017',3
        union select 'val2', 'a2','2016',3
        union select 'val2', 'a2','2017',5
    ) list
    where list.y = 2017
    group by k,y
    ) sub2 on sub1.k=sub2.k
    and sub2.av-sub1.av >0

Collectives™ on Stack Overflow

Error in SQL. Different values when using count

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related