union and top operator with order

[mjd3000]

I have a problem with my SQL. I am trying to order a top expression with a union, but the union prevents me from using order by. Here is my SQL before the union. How do I change this to work with order by?

SELECT top 5 COUNT(*) AS 'TotalEnquiries',
createdbyname as 'Name',

round(100 * CAST(COUNT(*) as float) /
(SELECT COUNT(*) AS Expr1
FROM FilteredLead
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)), 0) AS 'Percentage'
FROM FilteredLead

where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)

GROUP BY createdbyname

 

[markros]

What SQL Server you're using and where is the UNION part here? Also, how do you want to order the result?

 

[mjd3000]

I'm using SQL 2005..here is the full SQL including the union :

--select * from
--(
SELECT top 5 COUNT(*) AS 'TotalEnquiries',
createdbyname as 'Name',

round(100 * CAST(COUNT(*) as float) /
(SELECT COUNT(*) AS Expr1
FROM FilteredLead
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)), 0) AS 'Percentage'
FROM FilteredLead

where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)

GROUP BY createdbyname

--order by 'TotalEnquiries' desc) as d

union

select
(
SELECT SUM(T2.Expr1) FROM
(
(SELECT COUNT(*) AS Expr1
FROM FilteredLead
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
GROUP BY createdbyname)
) t2) -

(SELECT SUM(T1.Expr1) FROM
(
(SELECT top 5 COUNT(*) AS Expr1
FROM FilteredLead
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
GROUP BY createdbyname order by count(*) desc)) t1)

, 'Other',

round(100*(cast(((SELECT SUM(T2.Expr1) FROM
(
(SELECT COUNT(*) AS Expr1
FROM FilteredLead
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
GROUP BY createdbyname)
) t2) -

(SELECT SUM(T1.Expr1) FROM
(
(SELECT top 5 COUNT(*) AS Expr1
FROM FilteredLead
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
GROUP BY createdbyname order by count(*) desc)) t1)) as float))/

(SELECT COUNT(*) AS Expr1
FROM FilteredLead
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)), 0)

ORDER BY count(*) desc

 

[gmmastros]

Are you running this from a stored procedure where we can have multiple queries? If not.... I would encourage it.

I assume this is part of another question where you say:

[tt][blue]
No %
Claire Marshall 37 22
Frances Kennedy 35 21
Ken Fletcher 25 15
guestuser User 20 12
Naomi Manuel 19 11

And I want this output :
No %
Claire Marshall 37 22
Frances Kennedy 35 21
Ken Fletcher 25 15
guestuser User 20 12
Naomi Manuel 19 11
[!]Other 32 19 [/!]
[/blue][/tt]

Just look at all the extra coding involved just to get the 'Other' row. If you do this work in a stored procedure, I would recommend a couple changes. It should make the query 100times easier to understand.

My approach would be...

Create a temp table that aggregates your data... which appears to be the count of rows grouped by user name.

Then, perform some simple calculations and finally return the data to the front end app.

The following code is not tested, but should be pretty close to what you want.

-- Get all data in to temp table first.

Declare @Temp Table(CreatedByName VarChar(100), Rows Int)
		
Insert 
Into    @Temp(CreatedByName, Rows)
SELECT	CreatedByName,
        COUNT(*) AS Expr1
FROM	FilteredLead
where	createdonutc between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
					And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
GROUP BY createdbyname

-- Calculate the total count
Declare @TotalRows Int

Select @TotalRows = Sum(Rows)
From   @Temp

-- Calculate the count for the top 5 (used later in 'other' row)
Declare @TotalTop5 Int

Select  @TotalTop5 = Sum(Rows)
From    (
        Select Top 5 Rows
        From   @Temp
        Order By Rows DESC
        ) As A

-- Return the data.
Select  CreatedByName, Rows, Percentage
From    (
        Select Top 5 CreatedByName, Rows, 100.0 * Rows / @TotalRows As Percentage
        From   @Temp
        Order By Rows DESC
        ) As A

Union All

Select 'Other', @TotalRows - @TotalTop5, 100.0 * (@TotalRows - @TotalTop5) / @TotalRows

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom