Showing posts with label total. Show all posts
Showing posts with label total. Show all posts

Sunday, March 25, 2012

Computing a Grand Total

I have inherited a table that has entries such as:
AccountName, [Invoice No], [Sales Price] Qty, etc...
I would like to have a SubTotal and GrandTotal. So, I have a select
statement something like this:
Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty as
SubTotal
FROM ...
That works fine, I get an extra column with the product of Qty and Sales
Price.
However, how would I get a Grand Total per invoice? I can do it in VB.Net,
but would like to see how to do it in SQL.
TIA,
PaoloCOMPUTE SUM(SubTotal) BY [Invoice No]
Here's the Books ONline URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/69009df2-dba5-4bcb-b2ae-
a7502537cb3e.htm
HTH. Ryan
"Paul" <PaulContactMe@.TheCornerStore.com> wrote in message
news:LesBf.1078$v81.173@.fe12.lga...
>I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
> as SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try
SELECT [Invoice No], SUM([Sales Price] * Qty)
FROM [YourTable]
GROUP BY [Invoice No]
If you want grand totals for AccountName and [Invoice No]
SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
FROM [YourTable]
GROUP BY AccountName, [Invoice No]
WITH ROLLUP
The out put of the above query will have rows like
AccountName [Invoice No] Total
---
Some Account NULL $3.50
A row with a NULL in the [Invoice No] column indicates the grand total
for that AccountName. You will also get a row like
AccountName [Invoice No] Total
---
NULL NULL $1500.78
which shows the total for all invoices and accounts.
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try following examples on pubs database. You will have to use COMPUTE clause
.
use pubs
go
select ord_num, stor_id, qty from sales
order by ord_num
compute sum(qty) by ord_num
--using computed expression query will look like.
select ord_num, stor_id, (qty * 2) as subtotal from sales
order by ord_num
compute sum(qty * 2) by ord_num
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||I agree with Mark. Using ROLLUP on a GROUP BY is the far better choice,
since it will always be returned in one result set. COMPUTE is pretty ugly
to deal with for the user program.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:799AF000-43FC-483A-94E1-D5DFC3FA3CEF@.microsoft.com...
> Try
> SELECT [Invoice No], SUM([Sales Price] * Qty)
> FROM [YourTable]
> GROUP BY [Invoice No]
> If you want grand totals for AccountName and [Invoice No]
> SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
> FROM [YourTable]
> GROUP BY AccountName, [Invoice No]
> WITH ROLLUP
> The out put of the above query will have rows like
> AccountName [Invoice No] Total
> ---
> Some Account NULL $3.50
> A row with a NULL in the [Invoice No] column indicates the grand total
> for that AccountName. You will also get a row like
>
> AccountName [Invoice No] Total
> ---
> NULL NULL $1500.78
> which shows the total for all invoices and accounts.
>
> --
> "Paul" wrote:
>

Thursday, March 22, 2012

Compute Sum (Again)

I have tried everything to get a total of one of the columns in this query
including Rollup/Cube and compute sum. The problem is that compute sum canno
t
be used with select into and one cannot do a sum(count(distinct...
Rollup and cube give hierarchies and combination totals which is not what I
need.
I need something that operates as this should sum(count(distinct
a11.Policy_id)) but cannot find any function to do it. Can anyone please hel
p.
If one looks @. my select list, the final item is...
count(distinct(a11.Policy_id)) WJXBFS1
select a16.W_id W_id,
max(a16.w_desc) w_desc,
a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
a13.Pr_Group_id Pr_Group_id,
max(a13.Pr_group_desc) Pr_group_desc,
a12.Po_corp_unit_id Po_corp_unit_id,
count(distinct(a11.Policy_id)) WJXBFS1
into #ZZT4Z010YTNMD00B
from fat_bse_po_risk_detail a11
join POt_lu_policy a12
on (a11.Policy_id = a12.Policy_id)
join prt_lu_product a13
on (a11.product_id = a13.product_id)
join POv_lu_tr_Business_Type a14
on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
join TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
join TIt_lu_day a16
on (a11.Cur_trn_dt = a16.Cur_trn_dt)
where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Po_status_id in ('A')
and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a16.W_id in (select fiftytwo_w_roll_id from
tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
group by a16.W_id,
a14.Po_tr_bus_cat_id,
a13.Pr_Group_id,
a12.Po_corp_unit_idPlease explain what was wrong with the answer I gave yesterday.
The best way to get help with a problem like this is to post DDL (CREATE
TABLE statement(s)), sample data (INSERT statements) and show your required
end result. Probably no need to post all your tables, a simplified example
including keys and constraints should do it. If you do that you should find
you get a helpful answer much faster. The following article should help:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Please explain what was wrong with the answer I gave yesterday.
The best way to get help with a problem like this is to post DDL (CREATE
TABLE statement(s)), sample data (INSERT statements) and show your required
end result. Probably no need to post all your tables, a simplified example
including keys and constraints should do it. Please try to do that and you
should find you get a helpful answer much faster. The following article
should help:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||sum(count(distinct(a11.Policy_id))
this statement is odd and it is not allowed use a sub query in an agregade
function like sum
even if you could do so
if you have 1000 of count a11.Policy_id then you get the result as 1000.1000
(it will sum the count for every row of result set)
do you realy want that? if so you can create function then sum it
create function thefunction(@.w_id nvarchar(20))
returns int
as
begin
declare @.count_id as int
select @.count_id=count(Policy_id)
from fat_bse_po_risk_detail
Return IsNull(@.count,0)
end
then
select sum(thefunction(@.w_id))...
"marcmc" wrote:

> I have tried everything to get a total of one of the columns in this query
> including Rollup/Cube and compute sum. The problem is that compute sum can
not
> be used with select into and one cannot do a sum(count(distinct...
> Rollup and cube give hierarchies and combination totals which is not what
I
> need.
> I need something that operates as this should sum(count(distinct
> a11.Policy_id)) but cannot find any function to do it. Can anyone please h
elp.
> If one looks @. my select list, the final item is...
> count(distinct(a11.Policy_id)) WJXBFS1
> select a16.W_id W_id,
> max(a16.w_desc) w_desc,
> a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
> max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
> a13.Pr_Group_id Pr_Group_id,
> max(a13.Pr_group_desc) Pr_group_desc,
> a12.Po_corp_unit_id Po_corp_unit_id,
> count(distinct(a11.Policy_id)) WJXBFS1
> into #ZZT4Z010YTNMD00B
> from fat_bse_po_risk_detail a11
> join POt_lu_policy a12
> on (a11.Policy_id = a12.Policy_id)
> join prt_lu_product a13
> on (a11.product_id = a13.product_id)
> join POv_lu_tr_Business_Type a14
> on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
> join TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> join TIt_lu_day a16
> on (a11.Cur_trn_dt = a16.Cur_trn_dt)
> where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Po_status_id in ('A')
> and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
> 120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
> and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a16.W_id in (select fiftytwo_w_roll_id from
> tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
> group by a16.W_id,
> a14.Po_tr_bus_cat_id,
> a13.Pr_Group_id,
> a12.Po_corp_unit_id

Sunday, March 11, 2012

Complicated Query with Subs for Grand Total

I'm trying to get the syntax right below. Know that what I'm trying to do is correct (I don't want to explain why or the entire thing but just need help with correcting the syntax issues on the join and stuff:

Errors:

Msg 156, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 205

Incorrect syntax near the keyword 'INNER'.

Msg 156, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 213

Incorrect syntax near the keyword 'as'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 219

Line 219: Incorrect syntax near 'branch'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 219

Line 219: Incorrect syntax near ')'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 221

Line 221: Incorrect syntax near ')'.

Select @.InHouse2 = SUM(InHouse2)

FROM

(Select SUM (CCsNewTable.SumOldCCs) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DC

INNER JOIN

( Select Company, FeeSchedule, SUM(OldCCs) SumOldCCs

INNER JOIN

(Select CC AS OldCCs from DC d

WHERE d.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND d.EnteredDate IS NOT NULL

AND Type = 'CC'

AND ((branch = '00002' and customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0) ))

AND pdc_OnHold IS NULL

GROUP BY CompanyNumber

) as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table

WHERE branch='00002'

AND Type = 'In-House'

AND ((branch = '00002' and customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

)

Also tried adding aliases to distinguish the difference between DC and DC with d and dd

Select @.InHouse2 = SUM(InHouse2)

FROM

(Select SUM (CCsNewTable.SumOldCCs) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DCR d

INNER JOIN

( Select Company, FeeSchedule, SUM(OldCCs) SumOldCCs

INNER JOIN

(Select CC AS OldCCs from DCR dd

WHERE dd.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dd.EnteredDate IS NOT NULL

AND dd.Type = 'CC'

AND ((dd.branch = '00002' and dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0) ))

AND dd.pdc_OnHold IS NULL

GROUP BY dd.CompanyNumber

) as CCsNewTable ON d.CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table

WHERE d.branch='00002'

AND d.Type = 'In-House'

AND ((d.branch = '00002' and d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

)

|||You need to specify the JOIN conditions wiht your ON statement after the end of the subquery you are joining to, as I did in the part of the sub query:

as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

HTH
|||

My aliases and other stuff are still screwed up...need some help on this one...I can't see it:

DECLARE @.today DATETIME,

@.datefirst datetime,

@.Posted money,

@.GrossGoal money,

@.OldPDCs money,

@.NewPDCs money,

@.OldCCs money,

@.NewCCs money,

@.InHouse1 money,

@.InHouse2 money,

@.Var1 money

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'

SET @.datefirst = @.@.DATEFIRST

Select @.InHouse2 = SUM(inhouse1table.InHouse2)

FROM

(Select SUM (InHouse2Table.InHouse2) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DC d

INNER JOIN

( Select branch, Type, CustomerNumber, CustomerName, SUM(OldCCs) * FeeSchedule as InHouse2

FROM DC ddd

INNER JOIN

(Select CustomerNumber, CC AS OldCCs from DC dd

WHERE dd.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dd.EnteredDate IS NOT NULL

AND dd.Type = 'CC'

AND ((dd.branch = '00002' and dd.CustomerNumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND dd.pdc_OnHold IS NULL

GROUP BY dd.CustomerNumber

)as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table on d.CustomerNumber = inhouse2table.CustomerNumber

WHERE d.branch='00002'

AND d.Type = 'In-House'

AND ((d.branch = '00002' and d.CustomerNumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

) as inhouse1table

|||

Please check out another post here which explains what I'm doing which was one step before this post:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21753502.html

|||Is this a college assignment or something? Wh are you asking the same questions as dba123? If its for school, I cannot help you.|||are you out of your mind? does this look like a college assignement? something this complicated? What do you mean same questions...this is not the same. Is this too complicated for you to help me with? This is Grand Total that I have to mimic from a crazy excel spreadsheet from our finance dept....|||

this is a complete different problem than you saw from dba123...if you read you'll realize this. Just check this out, OK then come back with some useful replies please:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21753502.html

|||

that's ok pal, I fixed it...

DECLARE @.today DATETIME,
@.datefirst datetime,
@.Posted money,
@.GrossGoal money,
@.OldPDCs money,
@.NewPDCs money,
@.OldCCs money,
@.NewCCs money,
@.InHouse1 money,
@.InHouse2 money,
@.Var1 money

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

Select @.InHouse2 = SUM(InHouse2)
FROM
(Select CCsOldTable.SumOldCCs * CASE WHEN d1.FeeSchedule = 0 THEN
1
WHEN d1.FeeSchedule = 1 THEN
(d1.FeeSchedule / 100)
ELSE
(FeeSchedule / 100)
END AS InHouse2
FROM DCR d1
INNER JOIN
(Select d2.CustomerNumber, SUM(CCsQuery.OldCCs) AS SumOldCCs
FROM DCR d2
INNER JOIN
-- NewCCs Query
(Select CustomerNumber, CC AS OldCCs
FROM DCR d3
WHERE d3.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d3.Type = 'CC'
AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))
OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))
AND d3.dc_OnHoldDate IS NULL
GROUP BY d3.CustomerNumber, d3.CC)as CCsQuery on CCsQuery.CustomerNumber = d2.CustomerNumber
GROUP BY d2.CustomerNumber
) as CCsOldTable ON d1.CustomerNumber = CCsOldTable.CustomerNumber

GROUP BY d1.CustomerNumber, CCsOldTable.SumOldCCs, d1.FeeSchedule
) as z

Select @.InHouse2

|||

Sorry, did not mean to offend you, I was just wondering why you are using the same tables and queries. Hey, some college courses can get complicated now

Hope I was some help. Btw, you would probably be better (and quicker) to use a single join with an OR here instead of using IN, which is slow:

AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))
OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

|||

thanks, sorry, yea, I was a bit irritated but that's cool.

Can you help me with your explanation ion using the single join with OR instead of IN ?

|||I figured it out....|||Sorry, that was the last thing I posted before logging off.

Thursday, March 8, 2012

COMPLEX sql query PLEASE HELP!

I cant get "order by" to work in this sql query..
I use this query:

"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"
and I want to add this some where to get 12 random records: "ORDER BY NewID()"

I tried this:"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()""
but get the error:
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"

I can′t figure out how I should write the query..
Somebody have any ideas??
/Radiwoi

Try something like this:
SELECT TOP 12 name, total FROM (SELECT name, total = COUNT(*) FROM products WHERE kat = 'music' GROUP BY name) AS groupedSet ORDER BY NEWID()

The subquery generates a set in the output format that you want, the main query just orders it randomly and restricts it to 12 rows.

Does this help?
|||Thanks for your answer and thanks for your help It worked perfect.
you are KING!!!

Wednesday, March 7, 2012

complex report

have the following data from a query
age sex
39 M
48 F
14 M
etc...
need to be displayed as
Age Group Number of Male Number of Female Total in Age Group
0 -2 2 3
5
3 - 9 24 33
57
....
70 and above 12 11
23
Total Males ##
Total Females ##
Total
##
Any suggestions would be helpful.This question is better geared towards a SQL forum rather then reporting
services.
Search online for the SQL syntax of Case. You'll want to create a select
statment where you do something similar to this.
SELECT '0-2' as Age Group, SUM(CASE age>0 AND age < 2 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>0 AND age < 2 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 0 AND age < 2) as Total in Age Group
UNION
SELECT '3-9' as Age Group, SUM(CASE age>3 AND age < 9 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>3 AND age < 9 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 3 AND age < 9) as Total in Age Group
And so on and so forth for each of the age/sex ranges.
-Michael Abair
Programmer Analyst
Chicos FAS Inc
"Chop" <Chop@.discussions.microsoft.com> wrote in message
news:0D4FB1BC-D77D-4E6B-A728-B798B6EAF6E4@.microsoft.com...
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.|||You also have the option of creating fields within RS; for the values
use:
iif(fields!age.value >=0 and fields!age.value < 3, "0 - 2",
iif(fields!age.value < 10, "3-9", ...))
Then use a matrix item and use the calculated field as your row group,
sex as your column group.
Chop wrote:
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.

Saturday, February 25, 2012

Complex Query - Part II

I am passing the following query:
select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
else 0 end) as 'Closed',
sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
from ticket where location in (select location from location where
lstate = 'mp')
and received_date between '5/1/2004' and '5/10/2004' group by type
My requirement is for totals of each column generated, suggest. I am
using an automated asp script written by me for generating dynamic sql
reports, for which i just need to give the sql statement, suggest how to
get totals of each column generated automatically if it is of numeric
value and to ignore totals if it is of non numeric value.
Through sql query only.
Part II because my earlier post on May 10, 2004 was not answered.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
On Wed, 16 Jun 2004 23:03:50 -0700, Preet Kanwaljit Singh Shergill wrote:

>
>I am passing the following query:
>select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
>else 0 end) as 'Closed',
>sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
>sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
>from ticket where location in (select location from location where
>lstate = 'mp')
>and received_date between '5/1/2004' and '5/10/2004' group by type
>My requirement is for totals of each column generated, suggest. I am
>using an automated asp script written by me for generating dynamic sql
>reports, for which i just need to give the sql statement, suggest how to
>get totals of each column generated automatically if it is of numeric
>value and to ignore totals if it is of non numeric value.
>Through sql query only.
>
>Part II because my earlier post on May 10, 2004 was not answered.
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
Hi Preet,
If I understand your question correctly, you can either use this:
SELECT type, COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
GROUP BY type
UNION ALL
SELECT 'Total', COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
(untested)
Or you can look up ROLLUP in Books Online and see if that helps.
Final notes:
1) I changed the ambiguous date format you used to the recommended
YYYYMMDD format. Check if I didn't exchange the DD and MM parts of your
query.
2) I *think* that the subquery for location can be changed to an inner
join, but I'd have to know your table structure to be sure. The inner join
might yield better performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks a ton, it does solve my problem.
With this solution my SQL based reporting will be even faster than
before with minimal network traffic.
I no longer have to bother about html lipstick work.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!