>sir now i have these codes
>
>

>text TO query TEXTMERGE noshow
>
>SELECT data.acc_code,data.acc_name,
>sum(data.qty)as qty,
>sum(data.cr_amount)as c_ramount
>from (
>
>SELECT distinct(acc_code) as acc_code ,max(acc_name) as acc_name, qty, cr_amount
>	from crsalp where substring(acc_code,1,2)='32'  and type='P'
>
>union all
>
>SELECT distinct(acc_code) as acc_code ,max(acc_name) as acc_name, qty, cr_amount
>	from cashsalp where substring(acc_code,1,2)='31'  and type='P'
>) as data
>group by
>data.acc_code
>
>ENDTEXT
>
>

>
>but it says:
>
>Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'crsalp.acc_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


OK, you made some other changes. In your original query, you were doing a GROUP BY in both parts of the UNION in the inner subquery. This time you only have a GROUP BY on the outside.

When you get that error, in this case, it's because you were doing an aggregation (max) on acc_name, but you didn't specify a GROUP BY on the other columns included in the SELECT list that weren't being aggregated.

Here's my advice...make sure you get the code on the inside (the two queries with the UNION) working correctly. Then try the query on the outside. You're trying to accomplish too much at once, so it would be best to do this in steps until you get a firm handle on everything.