Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Sum fields in query
Message
From
15/11/2016 05:02:18
 
 
To
15/11/2016 04:56:36
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01643456
Message ID:
01643462
Views:
38
GROUP BY code not GROUP BY acc_code

>Thanks my professor,
>
>Now it has problem on this line
>
>select code as acc_code,  0 as op_qty, 0 as pr_qty, sum(qty) as sl_qty, 0 as sr_qty from cashsalp  where  code like '32%' and LEN(acc_code)=7
>       GROUP BY acc_code
>
>
>Msg 8120, Level 16, State 1, Line 10
>Column 'cashsalp.code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>
>Please help again
>
>
>
>
>
>>Try
>>
>>
>>
>>select data1.acc_code,
>>op_qty,
>>pr_qty,
>> sl_qty,
>> sr_qty,
>> op_qty + pr_qty as total_qty
>>	from
>>	(select acc_code, sum(qty) as op_qty, 0 as pr_qty, 0 as sl_qty, 0 as sr_qty from master where acc_code LIKE '32%' and LEN(acc_code)=7
>>        GROUP BY acc_code
>>	union all
>>	select acc_code, 0 as op_qty, sum(usedd) as pr_qty, 0 as sl_qty, 0 as sr_qty  from product  where acc_code like '32%'  and LEN(acc_code)=7
>>       GROUP BY acc_code
>>	union all
>>    select code as acc_code,  0 as op_qty, 0 as pr_qty, sum(qty) as sl_qty, 0 as sr_qty from cashsalp  where  code like '32%' and LEN(acc_code)=7
>>       GROUP BY acc_code
>>	union all
>>	select  acc_code, 0 as op_qty, 0 as pr_qty, sum(qty) as sl_qty, 0 as sr_qty from crsalp  where acc_code like '32%' and LEN(acc_code)=7
>>        group by acc_code
>>	union all
>>	select code as acc_code, 0 as op_qty, 0 as pr_qty, 0 as sl_qty, sum(qty) as sr_qty from srsalp  where code like '32%' and LEN(acc_code)=7
>>	group by acc_code)	
>>	as data1
>>	
>>	order by acc_code
>>
>>
>>>Dear Experts
>>>
>>>I have this query
>>>
>>>
>>>select data1.acc_code,
>>>SUM(case type when 1 then qty else 0 end )as op_qty,
>>>SUM(case type when 2 then qty else 0 end )as pr_qty,
>>>SUM(case type when 3 then qty else 0 end )as sl_qty,
>>>SUM(case type when 4 then qty else 0 end )as sr_qty,
>>>SUM(op_qty)+SUM(pr_qty)as total_qty
>>>	from
>>>	(select	acc_code,qty, 1 as type from master where SUBSTRING(acc_code,1,2)='32' and LEN(acc_code)=7
>>>	union all
>>>	select 	acc_code, usedd as qty,2 as type from product  where SUBSTRING(acc_code,1,2)='32'  and LEN(acc_code)=7
>>>	union all
>>>    select code as acc_code,qty, 3 as type from cashsalp  where SUBSTRING(code,1,2)='32' and LEN(acc_code)=7
>>>	union all
>>>	select 	acc_code, qty,3 as type from crsalp  where SUBSTRING(acc_code,1,2)='32' and LEN(acc_code)=7
>>>	union all
>>>	select 	code as acc_code, qty, 4 as type from srsalp  where SUBSTRING(code,1,2)='32' and LEN(acc_code)=7)
>>>		
>>>	as data1
>>>	group by acc_code
>>>	order by acc_code
>>>
>>>
>>>At Line 6, I am trying to get sum of more than one fields as
>>>
>>>
>>>SUM(op_qty)+SUM(pr_qty) as total_qty
>>>
>>>
>>>But it says:
>>>Msg 207, Level 16, State 1, Line 6
>>>Invalid column name 'open_qty'.
>>>
>>>Please help
Previous
Reply
Map
View

Click here to load this message in the networking platform