Prev: General maintenance ALERTS
Next: Can't set up new user in Sql 2000
From: tolcis on 2 Feb 2010 14:46
Hi,
I have the following query
SELECT JobName, count (*) as 'RunNumber'
FROM Jobs
WHERE JobStatus = 0
group by JobName
compute SUM (count (*))

In my case SUM has to calculate the total of all count(*) but I need
for it to appear in the column next to RunNumber and I need to be able
to assign a column name to SUM on the fly.
I know I can not do this:
compute SUM (count (*)) as 'total'
So, what would be my alternative?

T.

From: Plamen Ratchev on 2 Feb 2010 15:14
Here is one method:

SELECT JobName, RunNumber, SUM(RunNumber) OVER() AS total
FROM (
SELECT JobName, COUNT(*) AS RunNumber
FROM Jobs
WHERE JobStatus = 0
GROUP BY JobName) AS J;

--
Plamen Ratchev
http://www.SQLStudio.com
From: tolcis on 2 Feb 2010 16:53
On Feb 2, 3:14 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Here is one method:
>
> SELECT JobName, RunNumber, SUM(RunNumber) OVER() AS total
> FROM (
> SELECT JobName, COUNT(*) AS RunNumber
> FROM Jobs
> WHERE JobStatus = 0
> GROUP BY JobName) AS J;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

That is good but it shows up for every single row - I needed to show
up only once. Is there any way to do that?

T.
From: Erland Sommarskog on 2 Feb 2010 17:40
tolcis (nytollydba(a)gmail.com) writes:
> On Feb 2, 3:14�pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
>> Here is one method:
>>
>> SELECT JobName, RunNumber, SUM(RunNumber) OVER() AS total
>> FROM (
>> SELECT JobName, COUNT(*) AS RunNumber
>> FROM Jobs
>> WHERE JobStatus = 0
>> GROUP BY JobName) AS J;
>>
>> --
>> Plamen Ratchevhttp://www.SQLStudio.com
>
> That is good but it shows up for every single row - I needed to show
> up only once. Is there any way to do that?


WITH numbered AS (
SELECT JobName, RunNumber, SUM(RunNumber) OVER() AS total,
row_number () OVER(ORDER BY JobName) AS rowno
FROM (
SELECT JobName, COUNT(*) AS RunNumber
FROM Jobs
WHERE JobStatus = 0
GROUP BY JobName) AS J;
)
SELECT JohName, RunNumber,
CASE WHEN rowno = 1 THEN total END
FROM numbered

Not that I am sure that this is any better. But a result set is a table,
and all rows has equally many columns.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Plamen Ratchev on 2 Feb 2010 17:40
Try this:

SELECT JobName, RunNumber,
CASE WHEN ROW_NUMBER() OVER(ORDER BY JobName DESC) = 1
THEN SUM(RunNumber) OVER()
END AS total
FROM (
SELECT JobName, COUNT(*) AS RunNumber
FROM Jobs
WHERE JobStatus = 0
GROUP BY JobName) AS J
ORDER BY JobName;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: General maintenance ALERTS
Next: Can't set up new user in Sql 2000