Line with NULL value is disregarded by the SUM function?

Asked

Viewed 971 times

8

I participated in the IFNMG competition, a test prepared by the CEFET Foundation. I solved the following question and marked the letter C, but the feedback says that the correct is the letter A.

Question 31 Consider that the Notafiscalitem table is stored in a relational database manager system (DBMS), containing the following data.
Table: Notafiscalitem

inserir a descrição da imagem aqui

Then note the SQL command shown below.
Figure: SQL command

inserir a descrição da imagem aqui

The result produced by executing the SQL command, when considering the data presented in the Notafiscalitem table, is
a) 28,00 7,00 48,00
b) 28,00 NULL NULL
c) 48,00 7,00 48,00
d) 48,00 0,00 55,00
e) NULL NULL NULL

Does the statement "sum(qtd_item*vlr_unitario - vlr_desconto)" not add the first line for "null" in the "vlr_desconto" column? But that doesn’t depend on the DBMS you’re using?

sql database ansi

  • Could you transcribe the question in your question? Leaving it as an image impairs its publication, because many users cannot view the images and the search system does not index its content.

  • Okay, whatever is in text I’ll leave in text, but the table and the instruction is figure. Thank you

  • 2

    Yes, the NULL invalidates the rest of the calculation. (2*10 - NULL) will always return NULL. In order for it to be the option A it would have to have that way (there are other): sum(qtd_item*vlr_unitario - ISNULL(vlr_desconto, 0).

  • @Joãomartins actually in Mysql does not work that way...

  • @Sorack as he knew it was Mysql and not SQL Server?

  • 2

    The DBMS has not been specified and it is precisely at this point that I think the question is can be annulled.

  • 1

    @Joãomartins do not know which BDS, I gave just one example... in fact I think the issue deals with the ANSI standard

  • @Sorack Yes, but even so would have to have specified in the statement that it is the ANSI standard and not a specific DBMS, since depending on the DBMS the behavior may be different.

  • @Deniscaixeta understood. And yet it seems that it is a trick since it has different behaviors even

  • 1

    Relational banks have different behaviors regarding this null question. Firebird would return null in all three calculations, while mysql would ignore nulls.

Show 5 more comments

3 answers

6


In the Mysql grouped function documentation:

... group functions ignore NULL values.

OR IN FREE TRANSLATION:

... grouped functions ignore NULL values.

In the SQL Server the NULL of grouped columns invalidates the expression, so to answer precisely this question it would be necessary to know which the SGBD used, however according to this page (which I cannot confirm with 100% certainty the veracity) in the standard ANSI all aggregate functions except COUNT will ignore the values NULL when computing your results.

Schema (Mysql v5.7)

CREATE TABLE NotaFiscalItem(
  nro_nota_fiscal int,
  nro_item smallint,
  qtd_item smallint,
  vlr_unitario numeric(7, 2),
  vlr_desconto numeric(7, 2)
);

INSERT INTO NotaFiscalItem VALUES
(1, 1, 2, 10.00, NULL),
(1, 2, 2, 7.50, 2.00),
(1, 3, 1, 20.00, 5.00);

Query #1

SELECT SUM(qtd_item * vlr_unitario - vlr_desconto),
       SUM(vlr_desconto),
       SUM(qtd_item * vlr_unitario) - SUM(vlr_desconto)
  FROM NotaFiscalItem;

Upshot:

| SUM(qtd_item * vlr_unitario - vlr_desconto) | SUM(vlr_desconto) | SUM(qtd_item * vlr_unitario) - SUM(vlr_desconto) |
| ------------------------------------------- | ----------------- | ------------------------------------------------ |
| 28                                          | 7                 | 48                                               |

You can check the execution on DB Fiddle.

  • So the question should be dismissed, because should I have specified the DBMS? I will appeal.

  • @Deniscaixeta put extra information. In the pattern ANSI, apparently, the NULLis just ignored

4

To create the database:

CREATE TABLE NotaFiscalItem (
  nro_nota_fiscal integer,
  nro_item smallint,
  qtd_item smallint,
  vlr_unitario numeric(7, 2),
  vlr_desconto numeric(7, 2)
);

INSERT INTO NotaFiscalItem (nro_nota_fiscal, nro_item, qtd_item, vlr_unitario, vlr_desconto) VALUES (1, 1, 2, 10, NULL);
INSERT INTO NotaFiscalItem (nro_nota_fiscal, nro_item, qtd_item, vlr_unitario, vlr_desconto) VALUES (1, 2, 2, 7.50, 2.00);
INSERT INTO NotaFiscalItem (nro_nota_fiscal, nro_item, qtd_item, vlr_unitario, vlr_desconto) VALUES (1, 3, 1, 20.00, 5.00);

Transcribing the SELECT:

Select sum(qtd_item*vlr_unitario - vlr_desconto),
       sum(vlr_desconto),
       sum(qtd_item*vlr_unitario) - sum(vlr_desconto)
  from NotaFiscalItem

When running this (in Mysql 5.6), this response came:

28    7    48
  • 2

    :P by the image has a guy to be the postgres. No soen and in the dba if. there are questions on the subject as well. It is only complicated to infer whether it is about the database or about the ANSI standard.

0

Try to use COALESCE

ex:

SELECT coalesce(sum(qtd_item*vlr_unitario - vlr_desconto),0) from NotaFiscalItem

Browser other questions tagged sql database ansi

You are not signed in. Login or sign up in order to post.