rounding up percentages

[mjd3000]

I have the following SQL to calculate percentages which works fine, but because it is rounding down for example 0.7 to 0 and 15.6 to 15, the total is no where near 100%. Can anybody tell me how to do this so it rounds up correctly?


SELECT createdbyname, COUNT(*) AS 'Total', 100 * COUNT(*) /
(SELECT COUNT(*) AS Expr1
FROM FilteredLead) AS 'Percentage'
FROM FilteredLead AS FilteredLead_1
GROUP BY createdbyname
ORDER BY 'Total' DESC

 

[drlex]

Think you need to persuade SQL not to do an integer divide.

Does

SELECT     createdbyname, COUNT(*) AS 'Total', 100 * [b]CAST([/b]COUNT(*) [b]as float)[/b] /
                          (SELECT     COUNT(*) AS Expr1
                            FROM          FilteredLead) AS 'Percentage'
FROM         FilteredLead AS FilteredLead_1
GROUP BY createdbyname
ORDER BY 'Total' DESC

bring about the required figures?

soi la, soi carré

 

[bborissov]

SELECT createdbyname,
       COUNT(*) AS 'Total',
       100[COLOR=red][b].0[/b][/color] *
       COUNT(*) / (SELECT COUNT(*) AS Expr1
                          FROM FilteredLead) AS 'Percentage'
FROM         FilteredLead AS FilteredLead_1
GROUP BY createdbyname
ORDER BY 'Total' DESC

But I prefer this:

SELECT createdbyname,
       COUNT(*) AS 'Total',
       100[COLOR=red][b].0[/b][/color] *
       COUNT(*) / Tbl1.Cnt AS AS 'Percentage'
FROM FilteredLead
INNER JOIN (SELECT COUNT(*) AS Cnt
                   FROM FilteredLead) Tbl1
      ON 1 = 1
GROUP BY createdbyname
ORDER BY 'Total' DESC

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mjd3000]

Thanks drlex...I have one more question regarding this, I don't know if you could help me, but I currently have this output :
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

Here is my current SQL, how do I get the output I want?

--last week, all
SELECT createdbyname as 'Name',
COUNT(*) AS 'Total Enquiries',

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 'Total Enquiries' DESC

 

[mjd3000]

Sorry, my current SQL should read like this :

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

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 'Total Enquiries' DESC

 

[drlex]

You're welcome, although I prefer Boris's simpler (and no doubt faster) solution.

As to Q2, I would use a UNION with a query to give the remainder; a kind of anti-query, if you like.

soi la, soi carré

 

[mjd3000]

I am using a union, but having problems getting a sum for the top 5 values. Here is my SQL, can anybody help?

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

How do I get the sum value for these top 5 records, so that I can use this sum in calculations?

 

[drlex]

Wrap it in another SELECT?

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

soi la, soi carré

 

[mjd3000]

This now gives me the top 5 but ordered by name rather than by count(*) :

SELECT top 5 COUNT(*) AS 'Total Enquiries',
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

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', 1)


ORDER BY count(*) DESC