CODEWITHSUNDEEP
sqlgoogle-bigquerysubquery
SamR
SamR

Reputation: 77

Problem with "Scalar subquery produced more than one element" in BigQuery

I have a BigQuery with 3 columns: KLIENTNR, REGN_KONTO and BELOEP. I am trying to calculate the sum of the values in the BELOEP column based on the value inside the REGN_KONTO column. I have tried the following query:

SELECT
  rh.KLIENTNR,
  fk.KLIENTNAVN,
  fk.ORGANISASJONSNR,
  (SELECT sum(BELOEP) FROM `my_project.my_dataset.Regn_Hovedbok`
     WHERE REGN_KONTO >= 3000 AND REGN_KONTO <= 3999
     GROUP BY KLIENTNR )  AS INNTEKTER,
  (SELECT sum(BELOEP) FROM `my_project.my_dataset.Regn_Hovedbok`
     WHERE REGN_KONTO >= 4000 AND REGN_KONTO <= 9999
     GROUP BY KLIENTNR )  AS KOSTNADER

FROM `my_project.my_dataset.Regn_Hovedbok` AS rh
JOIN `my_project.my_dataset.Forvaltningsklient` AS fk  ON rh.KLIENTNR = fk.KLIENTNR
WHERE
  fk.TYPEKODE = 'AS'
GROUP BY
  rh.KLIENTNR,
  fk.KLIENTNAVN,
  fk.ORGANISASJONSNR
ORDER BY
  rh.KLIENTNR ;

I get the following error: Scalar subquery produced more than one element

Upvotes: 0

Views: 338

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Try below instead

SELECT
  rh.KLIENTNR,
  fk.KLIENTNAVN,
  fk.ORGANISASJONSNR,
  (SELECT sum(BELOEP) FROM `my_project.my_dataset.Regn_Hovedbok`
     WHERE REGN_KONTO >= 3000 AND REGN_KONTO <= 3999
     AND KLIENTNR = rh.KLIENTNR)  AS INNTEKTER,
  (SELECT sum(BELOEP) FROM `my_project.my_dataset.Regn_Hovedbok`
     WHERE REGN_KONTO >= 4000 AND REGN_KONTO <= 9999
     AND KLIENTNR = rh.KLIENTNR)  AS KOSTNADER
FROM `my_project.my_dataset.Regn_Hovedbok` AS rh
JOIN `my_project.my_dataset.Forvaltningsklient` AS fk  
ON rh.KLIENTNR = fk.KLIENTNR
WHERE
  fk.TYPEKODE = 'AS'
GROUP BY
  rh.KLIENTNR,
  fk.KLIENTNAVN,
  fk.ORGANISASJONSNR
ORDER BY
  rh.KLIENTNR

the reason your original query is failing is because your two subqueries return multiple rows as a result of group by - instead, I think you meant to calculate the sum only for respective client of outside grouping.

Also, consider below further "refactored" version - but it is really depends on what logic you have for your SUMs (the above version respects ignores JOIN while doing SUM, while below version - respects it. But still, depends on what the nature of your data and join - result potentially can be the same)

SELECT
  rh.KLIENTNR,
  fk.KLIENTNAVN,
  fk.ORGANISASJONSNR,
  SUM(IF(REGN_KONTO BETWEEN 3000 AND 3999, BELOEP, 0)) AS INNTEKTER,
  SUM(IF(REGN_KONTO BETWEEN 4000 AND 9999, BELOEP, 0)) AS KOSTNADER
FROM `my_project.my_dataset.Regn_Hovedbok` AS rh
JOIN `my_project.my_dataset.Forvaltningsklient` AS fk  
ON rh.KLIENTNR = fk.KLIENTNR
WHERE
  fk.TYPEKODE = 'AS'
GROUP BY
  rh.KLIENTNR,
  fk.KLIENTNAVN,
  fk.ORGANISASJONSNR
ORDER BY
  rh.KLIENTNR ;

Upvotes: 1

Related Questions