SQL query to fetch data from one table with different condition

Question

I am beginner of SQL developer and try to make such queries.
i have only one table in which all data. Now I want data as per calculation.

Table Structure is below:

Create table DataTable
(
id numeric(18,0),
DebitNoteNo varchar(20),
TotalAmt numeric(18,0),
Status char(4),
LineNumber numeric(18,0)  
) 

Insert into DataTable values(1,'DB001',200,'C',1)
Insert into DataTable values(2,'DB001',100,'C',2)
Insert into DataTable values(3,'DB001',300,'C',2)

Insert into DataTable values(4,'DB002',500,'C',1)
Insert into DataTable values(5,'DB002',100,'C',4)

Insert into DataTable values(6,'DB003',200,'S',2)
Insert into DataTable values(7,'DB003',300,'S',4)
Insert into DataTable values(8,'DB003',400,'S',5)

Insert into DataTable values(9,'DB003',200,'C',1)
Insert into DataTable values(10,'DB003',100,'C',3)
Insert into DataTable values(11,'DB003',700,'C',8)
Insert into DataTable values(12,'DB003',100,'C',5)

Insert into DataTable values(13,'DB004',800,'E',1)
Insert into DataTable values(14,'DB004',100,'E',5)
Insert into DataTable values(15,'DB004',200,'E',6)

I want output as below:

            Distinct
Col1        DebitNoteNoCount   TotalLineItem    TotalCount 
TotalFlagC    3                   9               2300
TotalFlagS    1                   3               900
TotalFlagE    1                   3               1100

Now in this output
Column 1 is fixed,
Column 2 is Count of Distinct DebitNote No
Column 3 is Count of Total Rows
Column 4 is Sum of TotalAmt

my row is also fixed (Three rows)

col1 is my where condition ex.

TotalFlagC =  where status = 'C'
TotalFlagS =  where status = 'S'

I do this with union but I think it takes more time. So please give me any other solutions.

SQL Fiddle

Answer 1

No need for UNION.

SELECT CONCAT('TotalFlag',STATUS), 
       COUNT(DISTINCT DEBITNOTENO), 
       COUNT(LineNumber) AS TotalLineItem,
       SUM(TOTALAMT) AS TotalCount
  FROM DataTable
GROUP BY
       STATUS;

SQL Fiddle

Answer 2

select distinct 'TotalFlag'+' '+Status,count(distinct DebitNoteNo)DEBITNOTENO ,count(LineNumber)TotalLineItem ,sum(TotalAmt)TotalCount from DataTable group by Status