CODEWITHSUNDEEP
sqlfirebird
vp_arth
vp_arth

Reputation: 14982

Firebird group clause

I can't to understand Firebird group logic

Query:

SELECT t.id FROM T1 t 
INNER JOIN T2 j  ON j.id = t.jid 
WHERE t.id = 1
GROUP BY t.id

works perfectly

But when I try to get other fields:

SELECT * FROM T1 t 
INNER JOIN T2 j  ON j.id = t.jid 
WHERE t.id = 1
GROUP BY t.id

I get error:

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Upvotes: 2

Views: 579

Answers (3)

shree.pat18
shree.pat18

Reputation: 21757

When you use GROUP BY in your query, the field or fields specified are used as 'keys', and data rows are grouped based on unique combinations of those 2 fields. In the result set, every such unique combination has one and only one row.

In your case, the only identifier in the group is t.id. Now consider that you have 2 records in the table, both with t.id = 1, but having different values for another column, say, t.name. If you try to select both id and name columns, it directly contradicts the constraint that one group can have only one row. That is why you cannot select any field apart from the group key.

For aggregate functions it is different. That is because, when you sum or count values or get the maximum, you are basically performing that operation only based on the id field, effectively ignoring the data in the other columns. So, there is no issue because there can only be one answer to, say, count of all names with a particular id.

In conclusion, if you want to show a column in the results, you need to group by it. This will however, make the grouping more granular, which may not be desirable. In that case, you can do something like this:

select * from T1 t
where t.id in
(SELECT t.id FROM T1 t 
 INNER JOIN T2 j  ON j.id = t.jid 
 WHERE t.id = 1
 GROUP BY t.id)

Upvotes: 1

AK47
AK47

Reputation: 3797

SELECT * FROM T1 t 
INNER JOIN T2 j  ON j.id = t.jid 
WHERE t.id = 1
GROUP BY t.id

This will not execute,cause you have used t.id in group by, So all your columns in select clause should be using aggregate function , else those should be included in group by clause. Select * means you are selecting all columns, so all columns except t.id are neither in group by nor in aggregate function.

Try this link, How to use GROUP BY in firebird

Upvotes: 1

MikkaRin
MikkaRin

Reputation: 3084

When you using GROUP BY clause in SELECT you should use only aggreagted functions or columns that listed in GROUP BY clause. More about GROUP BY clause:http://www.firebirdsql.org/manual/nullguide-aggrfunc.html

As example:

SELECT Max(t.jid), t.id FROM T1 t 
INNER JOIN T2 j  ON j.id = t.jid 
WHERE t.id = 1
GROUP BY t.id

Upvotes: 1

Related Questions