CODEWITHSUNDEEP
sql
user1282609
user1282609

Reputation: 575

Fetch the total from the result of a query

The query i am using is

select convert(varchar(10), sales.saledate, 103) [SaleDate], SUM(sales.Domestic)    [Domestic], SUM(sales.Export) [Export], SUM(sales.Import) [Import], 
SUM(sales.Value) [Value], Sum(sales.Cancelled) [Cancelled], sum(sales.cancelledValue) [CancelledValue], SUM(sales.totalValue) [TotalValue]
from
(
select max(j.SaleDate) SaleDate,
case when max(oc.Code) = 'AU' and max(dc.Code) = 'AU' then 1 else 0 end [Domestic],
case when max(oc.Code) = 'AU' and max(dc.Code) <> 'AU' then 1 else 0 end [Export],
case when max(oc.Code) <> 'AU' and max(dc.Code) = 'AU' then 1 else 0 end [Import],
1 [Total],
max(ic.Total) [Value],
case when max(c.CancelDate) is not null then 1 else 0 end [Cancelled],
case when max(c.CancelDate) is not null then max(ic.Total) else 0 end [CancelledValue],
case when max(c.CancelDate) is null then max(ic.Total) else 0 end [TotalValue]
from invoices i
left join Jobs j on i.JobKey = j.JobKey
inner join tasks t on j.jobkey = t.jobkey
inner join Consignments c on t.TaskKey = c.consignmentkey
inner join places op on c.originplacekey = op.placekey
inner join places dp on c.destinationplacekey = dp.placekey
inner join places oC on dbo.ParentPlaceKey(c.originPlaceKey) = oc.placekey
inner join places dC on dbo.ParentPlaceKey(c.destinationplacekey) = dc.placekey
left join (select consignmentKey, sum(Value) [Value] from ConsignmentItems ci group by       consignmentkey ) ci on ci.ConsignmentKey = c.ConsignmentKey
left join (select invoicekey, sum(case when ci.ChargeItemKey = 'FRT_SLL' then oc.Value     else 0 end) [Freight], 
sum(case when ci.ChargeItemKey = 'WTY_SLL' then oc.Value else 0 end) [Warranty],
sum(case when ci.ChargeType = 4 then oc.Value else 0 end) [Total] from InvoiceCharges  ic
left join OptionCharges oc on ic.OptionChargeKey = oc.OptionChargeKey
left join ChargeItems ci on oc.ChargeItemKey = ci.ChargeItemKey
group by invoicekey
) ic on ic.InvoiceKey = i.InvoiceKey
where 
j.SaleDate >= '01-Apr-2013' and j.SaleDate <= '10-May-2013'
and
j.operationalstorekey = dbo.StoreCode('AU-WEB')
and j.saledate is not null and SelectedOptionKey is not null
group by j.jobkey
) sales
group by convert(varchar(10), sales.saledate, 103)
order by max(sales.saledate)

The result of a sql query is

SaleDate     Domestic  Export Import     Value   Cancelled CancelledValue  Totalvalue
11/04/2013      1       0       0       47.200      0       0.0000          47.2000
16/04/2013      6       0       0       249.750     0       0.0000          249.7500
22/04/2013      0       1       0       223.480     0       0.0000          223.4800
23/04/2013      0       3       0       670.440     0       0.0000          670.4400

I want result like (want to add the TOTALS at the end)

SaleDate     Domestic  Export Import     Value   Cancelled CancelledValue  Totalvalue
11/04/2013      1       0       0       47.200      0       0.0000          47.2000
16/04/2013      6       0       0       249.750     0       0.0000          249.7500
22/04/2013      0       1       0       223.480     0       0.0000          223.4800
23/04/2013      0       3       0       670.440     0       0.0000          670.4400
    TOTALS      7       4       0       1190.432    0       0               1190.432

Can anyone please tell me how to achieve this in above query, i am trying with temp tables which i dont want. Thanks.

Upvotes: 1

Views: 212

Answers (1)

MISJHA
MISJHA

Reputation: 1008

You can achieve this by adding an UNION at the end of your result. Something like:

... order by max(sales.saledate)
UNION
(SELECT "TOTALS", SUM(sales.Domestic) AS Domestic, SUM(sales.Export) AS Export,
SUM(sales.Import) AS Import, SUM(sales.Value) [Value], Sum(sales.Cancelled) AS Cancelled, sum(sales.cancelledValue) AS CancelledValue, SUM(sales.totalValue) AS TotalValue
FROM "your_big_query"...
WHERE ...
)

And this time remove the group by convert(varchar(10), sales.saledate, 103)

EDIT

Or you can use the GROUP BY Modifiers try to use this in the GROUP BY statement:

group by convert(varchar(10), sales.saledate, 103) WITH ROLLUP

You can read the official documentation here with some examples too.

Hope this works!!

Upvotes: 2

Related Questions