CODEWITHSUNDEEP
sql
daniel
daniel

Reputation: 587

SQL Basic Syntax

I have the following problem:

Problem

What happens if the query didn't ask for B in the select?. I think it would give an error because the aggregate is computed based on the values in the select clause.

I have the following relation schema and queries:

Suppose R(A,B) is a relation with a single tuple (NULL, NULL). 

SELECT  A,  COUNT(B)
FROM    R
GROUP   BY  A; 

SELECT  A,  COUNT(*)
FROM    R
GROUP   BY  A; 

SELECT  A,  SUM(B)
FROM    R
GROUP   BY  A;

The first query returns NULL and 0. I am not sure about what the second query returns. The aggregate COUNT(*) count the number of tuples in one table; however, I don't know what it does to a group. The third returns NULL,NULL

Upvotes: 1

Views: 289

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

The only rule about SELECT and GROUP BY is that the unaggregated columns in the SELECT must be in the GROUP BY (with very specific exceptions).

You can have columns in the GROUP BY that never appear in the SELECT. That is fine. It doesn't affect the definition of a group, but multiple rows may seem to have the same values in the GROUP BY columns.

Upvotes: 2

Related Questions