Using Aggregate Function in sql

Question

Why this query is not working:-

SELECT b.byhub, 
       Count(awbno) 
FROM   (SELECT awbno, 
               byhub, 
               entrydatetime, 
               Max(entrydatetime) 
        FROM   wbbooking 
        GROUP  BY awbno, 
                  byhub, 
                  entrydatetime) B 
       INNER JOIN (SELECT refno, 
                          Max(entrydatetime) 
                   FROM   wbmmreceiving 
                   GROUP  BY refno) R 
               ON B.awbno = R.refno 
WHERE  CONVERT(DATE, b.entrydatetime) BETWEEN '2016-10-13' AND '2016-10-13' 
GROUP  BY b.byhub

After Execute this query I have obtain this type of Error:-

Msg 8155, Level 16, State 2, Line 2
No column name was specified for column 4 of 'b'.
Msg 8155, Level 16, State 2, Line 3
No column name was specified for column 2 of 'r'.

What's the SQL engine that you are using? Is it case sensitive? B vs b? — SMA
– SMA, Commented Apr 1, 2017 at 16:41
Since you grouped by entrydatetime, what are you expecting Max(entrydatetime) to yield? — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Apr 1, 2017 at 16:44
Your query seems more complicated than needed for this purpose. — Gordon Linoff
– Gordon Linoff, Commented Apr 1, 2017 at 16:51

Olivier Jacot-Descombes · Accepted Answer · 2017-04-01 17:06:00Z

The errors occur, because you have not specified a name for the aggregate column Max(entrydatetime). Change it to Max(entrydatetime) AS max_entrydatetime.

This query is a bit strange. The subquery B selects entrydatetime as well as Max(entrydatetime). But since entrydatetime is included in the group by list, Max(entrydatetime) will always be the same as entrydattetime.

If you want the last entrydatetime per awbno and byhub, then don't group by entrydatetime and only include it as Max(entrydatetime) in the select list. If you really want to group by this date column, then don't include it as Max(entrydatetime).

What is the purpose of the second subquery? None of its returned columns are used except for joining. Is it needed for the count?

Why include a where-clause in the outer query? If you include it in the first subquery, it is more efficient and the entrydatetime column needs not to be returned any more.

Giorgos Altanis · Accepted Answer · 2017-04-01 16:41:26Z

1

Just specify column names for your subqueries, like this:

SELECT b.byhub, 
       Count(awbno) 
FROM   (SELECT awbno, 
               byhub, 
               entrydatetime, 
               Max(entrydatetime) max_entrydatetime
        FROM   wbbooking 
        GROUP  BY awbno, 
                  byhub, 
                  entrydatetime) B 
       INNER JOIN (SELECT refno, 
                          Max(entrydatetime) max_entrydatetime
                   FROM   wbmmreceiving 
                   GROUP  BY refno) R 
               ON B.awbno = R.refno 
WHERE  CONVERT(DATE, b.entrydatetime) BETWEEN '2016-10-13' AND '2016-10-13' 
GROUP  BY b.byhub

answered Apr 1, 2017 at 16:41

Giorgos Altanis

2,7701 gold badge15 silver badges14 bronze badges

Collectives™ on Stack Overflow

Using Aggregate Function in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related