Compare two values in same table

[budimir]

Guys,

 

How can I compare two values in same table.

servis.kalkulacija_stavke (
  id BIGINT(255) NOT NULL AUTO_INCREMENT,
  id_kalkulacija INT(255) NOT NULL,
  id_cjenika INT(255) NOT NULL,
  vrijeme TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  kataloski_broj VARCHAR(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  kategorija_artikla VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  grupa_proizvoda VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  podgrupa_proizvoda VARCHAR(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  cijena_EUR DECIMAL(20, 6) NOT NULL,
  cijena_KN DECIMAL(20, 6) NOT NULL,
  carina DECIMAL(20, 6) NOT NULL,
  spediter DECIMAL(20, 6) NOT NULL,
  banka DECIMAL(20, 6) NOT NULL,
  transport DECIMAL(20, 6) NOT NULL,
  nabavna_cijena DECIMAL(20, 6) NOT NULL,
  drezga_marza_po_grupi DECIMAL(20, 6) NOT NULL,
  drezga_zarada DECIMAL(20, 6) NOT NULL,
  neto_VPC DECIMAL(20, 6) NOT NULL,
  neto_MPC DECIMAL(20, 6) NOT NULL,
  trosak_firme DECIMAL(20, 6) NOT NULL,
  trosak_firme_p DECIMAL(20, 6) NOT NULL,
  diler_marza_po_grupi DECIMAL(20, 6) NOT NULL,
  preporucena_VPC DECIMAL(20, 6) NOT NULL,
  preporucena_MPC DECIMAL(20, 6) NOT NULL,
  zarada_diler_kn DECIMAL(20, 6) NOT NULL,
  zarada_diler_post DECIMAL(20, 6) NOT NULL,
  zarada_za_nas_kn DECIMAL(20, 6) NOT NULL,
  zarada_za_nas_post DECIMAL(20, 6) NOT NULL,
  brutto_zarada_za_nas_kn DECIMAL(20, 6) NOT NULL,
  brutto_zarada_za_nas_post DECIMAL(20, 6) NOT NULL,
  godina YEAR(4) NOT NULL,
  PRIMARY KEY (id),
  INDEX id (id),
  INDEX kat_br (kataloski_broj),
  INDEX kataloski_broj (kataloski_broj, kategorija_artikla, grupa_proizvoda, podgrupa_proizvoda)
)
ENGINE = MYISAM
AUTO_INCREMENT = 1119990
AVG_ROW_LENGTH = 264
CHARACTER SET utf8
COLLATE utf8_general_ci;

I want to compare field nabavna_cijena in last year and this year. How can I achieve that?

[Barand]

Do you mean

SELECT YEAR(vrijeme), SUM(nabavna_cijena) as total
FROM kalkulacija_stavke
WHERE YEAR(vrijeme) > YEAR(CURDATE()) -2
GROUP BY YEAR(vrijeme)

[budimir]

 

Do you mean

SELECT YEAR(vrijeme), SUM(nabavna_cijena) as total
FROM kalkulacija_stavke
WHERE YEAR(vrijeme) > YEAR(CURDATE()) -2
GROUP BY YEAR(vrijeme)

Barand, thanks for you're answer, but I would need something like:

 

If nabavna_cijena (last year) < nabavna_cijena (this year) as less

If nabavna_cijena (last year) > nabavna_cijena (this year) as more

[Barand]

How about

SELECT a.yr
 , a.tot as yr_tot
 , b.tot as prev_yr_tot
 , IF(a.tot>=b.tot, 'more','') as more
 , IF(a.tot< b.tot, 'less','') as less
 FROM (
    SELECT YEAR(vrijeme) as yr
     , SUM(nabavna_cijena) as tot
     FROM kalkulacija_stavke
     GROUP BY YEAR(vrijeme)
     ) as a
LEFT JOIN (
    SELECT YEAR(vrijeme) as yr
     , SUM(nabavna_cijena) as tot
     FROM kalkulacija_stavke
     GROUP BY YEAR(vrijeme)
     ) as b ON a.yr = b.yr + 1;

[budimir]

 

How about

SELECT a.yr
 , a.tot as yr_tot
 , b.tot as prev_yr_tot
 , IF(a.tot>=b.tot, 'more','') as more
 , IF(a.tot< b.tot, 'less','') as less
 FROM (
    SELECT YEAR(vrijeme) as yr
     , SUM(nabavna_cijena) as tot
     FROM kalkulacija_stavke
     GROUP BY YEAR(vrijeme)
     ) as a
LEFT JOIN (
    SELECT YEAR(vrijeme) as yr
     , SUM(nabavna_cijena) as tot
     FROM kalkulacija_stavke
     GROUP BY YEAR(vrijeme)
     ) as b ON a.yr = b.yr + 1;

Thanks Barand!