Mysql select where values not equal

Question

getting error:

1064 - You have an error in your SQL syntax;

 check the manual that corresponds to your MySQL 
 server version for the right syntax to use near 
 't1.pard_suma LIMIT 0, 25' at line 5

NO DISTINCT error:
#1054 - Unknown column 't1.pard_suma' in 'having clause' 

SELECT t1.invoice_nr
FROM irasai t1
LEFT JOIN apmokejimai t2 ON t1.invoice_nr = t2.invoice_nr
GROUP BY t1.id
HAVING SUM(DISTINCT t2.suma) <> t1.pard_suma

But columns exists!

How to fix this?

This something like I need, but I want to get all invoice_nr which pard_suma is higher then all apmokejimai.suma (summed them all by same invoice_nr) and that which invoice_nr don't exist in apkomejimai table

Thanks.

Answer 1

The HAVING clause is looking for a static value on the right side of <> comparison operator. So you will have to do something like this:

SELECT t1.*
FROM irasai t1
LEFT JOIN apmokejimai t2 ON t1.invoice_nr = t2.invoice_nr
GROUP BY t1.id
HAVING SUM(DISTINCT t2.suma) <> (
  SELECT MAX(pard_suma) 
  FROM irasai 
  WHERE invoice_nr = t1.invoice_nr 
  GROUP BY invoice_nr
)

Example: http://sqlfiddle.com/#!9/90760/6

create table irasai (
  id int,
  invoice_nr int,
  pard_suma int
);

create table apmokejimai (
  invoice_nr int,
  suma int
);

insert into irasai values (1, 1, 100);
insert into apmokejimai values (1, 20), (1, 40), (1, 40);

insert into irasai values (1, 1, 200);
insert into apmokejimai values (1, 40), (1, 80), (1, 81);

Notice that I deliberately entered data in apmokejimai that doesn't total up to 200.

In the SQL's right side of <>, I have created a sub-query that calculates a number that HAVING can compare with. I am guessing this is somewhat similar to what you are trying to do.

**Answering OP's question in comments*

create table irasai (
  id int,
  invoice_nr int,
  pard_suma int
);

create table apmokejimai (
  invoice_nr int,
  suma int
);

insert into irasai values (1, 1, 100);
insert into apmokejimai values (1, 20), (1, 40), (1, 40);

insert into irasai values (2, 2, 200);
insert into apmokejimai values (2, 40), (2, 80), (2, 81);

insert into irasai values (3, 3, 300);

select
  t1.invoice_nr,
  max(t1.pard_suma) as pardtotal,
  sum(t2.suma) as sumatotal
from irasai t1
left join apmokejimai t2 on t1.invoice_nr = t2.invoice_nr
group by invoice_nr
having pardtotal <> sumatotal or sumatotal is null

Example: http://sqlfiddle.com/#!9/fb331/3