CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
Brane
Brane

Reputation: 23

how can I select something else with count

I have a task to select name, surname and ID of a salesman and to count how many cars they've sold in the year 2010 in FireBird 2.5. Here is the database:

Salesman(salesmanID:int primary key, salesmanName:char(20), SalesmanSurname:char(20))
sale (SalesDate:Date, SalesmanID:foreignkey to salesman)

I've tried with this:

select s.count(*), sm.salesmanID, sm.salesmanName, sm.SalesmanSurname
from sale s inner join salesman on s.salesmanID=sm.salesmanID
where SalesDate between '1.1.2010' and '31.12.2010';

and error I get is:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Upvotes: 0

Views: 422

Answers (3)

Gökmen Saral
Gökmen Saral

Reputation: 38

Work this way

SELECT
    count(*),
    sm.salesmanID,
    sm.salesmanName,
    sm.SalesmanSurname
FROM
    sale s
INNER JOIN salesman sm ON s.salesmanID = sm.salesmanID
WHERE
    s.SalesDate BETWEEN '1.1.2010'
AND '31.12.2010'
GROUP BY
    sm.salesmanID,
    sm.salesmanName,
    sm.SalesmanSurname

Upvotes: 1

Turo
Turo

Reputation: 4914

Make the aggregate first (your're missing a group by) and then join teh result with the salesman table:

select s.salescount, s.salesmanID , sm.salesmanName, sm.SalesmanSurname from
(select count(*) as salescount, salesmanID
FROM sale where SalesDate between '1.1.2010' and '31.12.2010'
    group by salesmanID) as s inner join salesman sm on s.salesmanID=sm.salesmanID

Upvotes: 0

Kumar_Vikas
Kumar_Vikas

Reputation: 845

You are using s.count(*) which is not a valid syntax. This could one of the reason you are getting the error.

Also, I Don't see sm as alias on any table. Mutiple issues found here.

Upvotes: 0

Related Questions