current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -1 down vote favorite

I would like to select "extra" payments made in association with normal appointments for a one week period and SUM them up by contractor when calculating payroll. Here's what I attempted:

SELECT SUM(extras.amount), 
       appointments.contractor_id 
    FROM extras 
    INNER JOIN appointments 
    ON    extras.appt_id = appointments.appt_id 
    WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' 
    AND   appointments.time < CURRENT_TIMESTAMP 
    AND   appointments.confirmed = 1 
    AND   (appointments.status = 10 OR appointments.status = 12) 
    GROUP BY appointments.contractor_id;

I'd like to do this with just the one query, but I am getting this error from Postgres:

db=> SELECT SUM(extras.amount), appointments.contractor_id from extras inner join appointments on extras.appt_id=appointments.appt_id;

ERROR: column "appointments.contractor_id" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: SELECT SUM(extras.amount), appointments.contractor_id from e...

I've tried playing around with the order of the clauses since I do have contractor_id in the GROUP BY statement.

What am I missing? Thanks for any suggestions.

share|improve this question
    
Whats is it with the extra slash after appointments? –  Mihai 2 days ago
    
@Mihai total typo while copying and pasting. Thanks for pointing it out. –  Fawn 2 days ago
    
Do you use the command line? –  Mihai 2 days ago
1  
The full query should work,it seems to me the error does not refer to the full query.Does a simple select from a sngle table work?MAke sure you end the statement with ; –  Mihai 2 days ago
1  
Your query should work. there must be something going on that is not in your question. –  Erwin Brandstetter yesterday

2 Answers 2

up vote -1 down vote

seems to be god you can try

WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' AND appointments\ .time < CURRENT_TIMESTAMP AND appointments.confirmed = 1 AND (appointments.status = 10 OR appointments.status=12)

after the group by like ...

SELECT SUM(extras.amount), appointments.contractor_id FROM extras INNER JOIN appointments ON extras.appt_id=appointments.appt_id GROUP BY appointments.contractor_id having appointments.time > CURRENT_TIMESTAMP - interval '8 days' AND appointments\
.time < CURRENT_TIMESTAMP AND appointments.confirmed = 1 AND (appointments.status = 10 OR appointments.status=12) ;
share|improve this answer
    
thank you for the suggestion, but this seems to generate a similar error: db=> SELECT SUM(extras.amount), appointments.contractor_id FROM extras INNER JOIN appointments ON extras.appt_id=appointments.appt_id GROUP BY appointments.contractor_id having appointments.time > CURRENT_TIMESTAMP - db-> interval '8 days' AND appointments.time < CURRENT_TIMESTAMP AND appointments.confirmed = 1 AND (appointments.status = 10 OR appointments.status=12) –  Fawn 2 days ago
    
db-> ; ERROR: column "appointments.time" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...ppt_id GROUP BY appointments.contractor_id having appointmen... –  Fawn 2 days ago
    
and you tried with GROUP BY appointments.contractor_id , appointments.time , i dont know why i recive -1 if i m giving my best to help you guys –  Guillermo Nahuel Varelli yesterday
up vote -1 down vote

Try wrapping it inside another select.

SELECT SUM(amount),
   contractor_id
FROM
( 
SELECT SUM(extras.amount) as amount, 
   appointments.contractor_id 
FROM extras 
INNER JOIN appointments 
ON    extras.appt_id = appointments.appt_id 
WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' 
AND   appointments.time < CURRENT_TIMESTAMP 
AND   appointments.confirmed = 1 
AND   (appointments.status = 10 OR appointments.status = 12) 
) SOURCE
GROUP BY contractor_id;
share|improve this answer
    
thanks for your suggestion. This also produces the same error. db=> SELECT SUM(amount), contractor_id FROM ( SELECT SUM(extras.amount) as amount, db(> appointments.contractor_id FROM extras INNER JOIN appointments ON extras.appt_id = appointments.appt_id db(> WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' db(> AND appointments.time < CURRENT_TIMESTAMP db(> AND appointments.confirmed = 1 db(> AND (appointments.status = 10 OR appointments.status = 12) db(> ) SOURCE db-> GROUP BY contractor_id; –  Fawn 2 days ago
    
ERROR: column "appointments.contractor_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: appointments.contractor_id FROM extras INNER JOIN appoint... –  Fawn 2 days ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.