group by clause

[rrajarat]

I have the following select statement for my datawindow but powerbuilder won't accept it. I get a message saying that all the columns that aren't computed have to be in the group by clause. If I do this then the sum is pointless. I would like to have the sum in the select statement so that when I send the data to excel there is only one row for each part (using powerbuilder group function will not work for my purpose).

SELECT "prod_eff_yesterday"."part" ,
"prod_eff_yesterday"."std_crew" ,
"prod_eff_yesterday"."act_crew" ,
"prod_eff_yesterday"."scrap" ,
"prod_eff_yesterday"."downtime" AS downtime,
sum("prod_eff_yesterday"."qty&quot AS qty,
sum("prod_eff_yesterday"."std_run&quot AS std_run,
sum("prod_eff_yesterday"."act_run&quot AS act_run
FROM "prod_eff_yesterday"
GROUP BY "prod_eff_yesterday"."part"

 

[jagadis]

When you use a group by clause in a select statement, the valid group by clause should expect all the columns used in the select statement that dont have a aggregate function on them, to be available in the group by clause.
Sybase allows you to use such an erroneus query but powerbuilder doesnt allow this to happen. In your case the correct query will be:
SELECT "prod_eff_yesterday"."part" ,
"prod_eff_yesterday"."std_crew" ,
"prod_eff_yesterday"."act_crew" ,
"prod_eff_yesterday"."scrap" ,
"prod_eff_yesterday"."downtime" AS downtime,
sum("prod_eff_yesterday"."qty&quot AS qty,
sum("prod_eff_yesterday"."std_run&quot AS std_run,
sum("prod_eff_yesterday"."act_run&quot AS act_run
FROM
"prod_eff_yesterday"
GROUP BY
"prod_eff_yesterday"."part" ,
"prod_eff_yesterday"."std_crew" ,
"prod_eff_yesterday"."act_crew" ,
"prod_eff_yesterday"."scrap" ,
"prod_eff_yesterday"."downtime"