>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
If it's not broken, fix it until it is.
My Blog