Hi gurus,

I am trying to find a solution to my current Sales Statistics debacle. It has been going on for quite some time now and so I want to put this one to sleep.

I have used the below script to try and list the outstanding Sales Orders in the system:
//script for outstanding sales orders in the system
use myDatabase
go
select t_citg, sum(t_amta) AS 'Outstanding Sales Orders'
from dbo.ttdsls041101
where t_dqua = 0 and t_oqua <> 0
group by t_citg
order by t_citg
go

I try using the below script and am wanting to try get one where it will list the "Order Intake (1)", "Orders Cancelled (2)" and "Orders Invoiced (3)" in one line, however my current tsql skills are not there yet, so if any of you are guru's at this, please feel free to post it as it is required?
//script for sales order history lines in the system
use myDatabase
go
select t_citg, sum(t_amta) AS 'Order Intake'
from dbo.ttdsls051101
where t_ckor = 1
group by t_citg, t_ckor
order by t_citg, r_ckor
go

I will appreciate any help I can get please?

Hi,

The question here is not much clear. Can you please tell, what is the problem you are facing while running the query? Are you getting any errors? Aren't you getting expected results?

I think requirement is to have single query that show below 4 columns (item Group and other 3 fields for sum of that item group)

Item Group
Sum of Order Intake
Sum of Orders Cancelled
Sum of Orders Invoiced


Not sure abt TSQL, but in general you can achieve this by using select from select statement and Join.

For Example:
Select *
from
(select t_citg, sum(t_amta) AS 'Order Intake'
from dbo.ttdsls051101
where t_ckor = 1
group by t_citg, t_ckor) as A,

(select t_citg, sum(t_amta) AS 'Order Cancelled'
from dbo.ttdsls051101
where t_ckor = 2
group by t_citg, t_ckor) AS B,
(select t_citg, sum(t_amta) AS 'Order Invoiced'
from dbo.ttdsls051101
where t_ckor = 3
group by t_citg, t_ckor) AS C
where
A.t_citg = B.t_citg and
A.t_citg = C.t_citg

thank you Manish, exactly what I am looking for.