would union all not be better ? Or at least a "fromview" column?
>>Dear Experts
>>
>>I have this query
>>
>>
>> SELECT distinct(data1.month)as month, SUM(data1.qty)as qty,SUM(data1.weight)as weight,SUM(data1.dr_amount)as dr_amount
>>from(
>>select MONTH(DATE) as month,SUM(qty)as qty,SUM(qty*weight)as weight,SUM(cr_amount) as dr_amount
>> from vcashsalp
>> where type='P'
>> group by month(date)
>> union
>> select MONTH(DATE) as month,SUM(qty)as qty,SUM(qty*weight)as weight,SUM(cr_amount) as dr_amount
>> from vcrsalp
>> where type='P'
>> group by month(date)
>> )
>> as data1
>>
>>
>>It says:
>>
>>Msg 8120, Level 16, State 1, Line 1
>>Column 'data1.month' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>>
>>
>>I am trying to get Monthwise sale.
>>
>>vcashsalp and vcrsalp are views.
>>
>>Please help
>
>Try
>
>
>SELECT data1.month as month, SUM(data1.qty)as qty,SUM(data1.weight)as weight,SUM(data1.dr_amount)as dr_amount
>from(
>select MONTH(DATE) as month,SUM(qty)as qty,SUM(qty*weight)as weight,SUM(cr_amount) as dr_amount
> from vcashsalp
> where type='P'
> group by month(date)
> union
> select MONTH(DATE) as month,SUM(qty)as qty,SUM(qty*weight)as weight,SUM(cr_amount) as dr_amount
> from vcrsalp
> where type='P'
> group by month(date)
> )
> as data1
>
>GROUP BY Month