SQL - Adding a count to select clause

Question

I have this query to show a list of trending (most searched) names on my website:

SELECT name, COUNT(*) AS total_trends
FROM trending_names
WHERE dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' // -7 days to Now()
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 10;

...and this is the kind of results I'm printing to screen: (numbers represent quantity of searches made)

Angelina Jolie        31,293
Rihanna               26,722
Lindsay Lohan         18,351
Brad Pitt             11,901

I would now like to change the numbers to percentages; so I really need to be getting the total count of all trending names within the last 7 days, to calculate the correct percentage.

Is there a way I can add a total count to this query, without adding an additional query?

Is there a specific reason you don't want to use an additional query? — Jeremy S
– Jeremy S, Commented Mar 8, 2012 at 21:53
@JeremyS: Not really. I thought by adding it to this query it would perform better, rather than having two queries... I'm probably wrong though??? — TheCarver
– TheCarver, Commented Mar 8, 2012 at 22:02

score 3 · Accepted Answer · 2012-03-08 21:56:16Z

3

You can do in single query :

Try Below :

SELECT name, COUNT(*) AS total_trends,
sum(if(dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' ,1,0)) as total_last_7_days,
((sum(if(dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' ,1,0)) /COUNT(*) ) *100)
as percentage                          // if you want to get only percentage
FROM trending_names
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 10;

edited Mar 8, 2012 at 21:56

answered Mar 8, 2012 at 21:50

user319198

Sign up to request clarification or add additional context in comments.

Comments

Victor · Accepted Answer · 2012-03-08 21:48:59Z

1

You can use a subquery :

SELECT name, ((COUNT(*)*100)/(SELECT COUNT(*) FROM trending_names))  AS total_trends
FROM trending_names
WHERE dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' // -7 days to Now()
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 10;

answered Mar 8, 2012 at 21:48

Victor

1636 bronze badges

2 Comments

Ben Over a year ago

Why are you grouping by name in your sub-select?

user319198 Over a year ago

It's possible with without subquery. Avoid subquerires untll no need for it

Steve Kass · Accepted Answer · 2012-03-09 04:18:22Z

1

I know you aren't running SQL Server, but some readers might be interested to see this compact solution that's possible (SQL Server 2008 or later). I'm not sure many people know you can have a windowed aggregate that aggregates an aggregate.

select
  name,
  100.0*count(*)/sum(count(*)) over () as pct_trends
from trending_names
where dateTime between getdate()-7 and getdate()
group by name;

answered Mar 9, 2012 at 4:18

Steve Kass

7,22422 silver badges28 bronze badges

Comments

swingbit · Accepted Answer · 2012-03-08 21:48:16Z

0

No, I don't think so. You do need to compute that total count on a separate (sub)select

answered Mar 8, 2012 at 21:48

swingbit

2,76520 silver badges38 bronze badges

Comments

RolandoMySQLDBA · Accepted Answer · 2012-03-08 22:02:57Z

0

SELECT name,(total_trends*100.0/sum_total_trends) pct_trends
FROM
(
    SELECT name, COUNT(*) AS total_trends 
    FROM trending_names 
    WHERE dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' // -7 days to Now() 
    GROUP BY name 
    WITH ROLLUP
) A,
(
    SELECT COUNT(*) AS sum_total_trends 
    FROM trending_names 
    WHERE dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' // -7 days to Now() 
) B;

answered Mar 8, 2012 at 22:02

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

Collectives™ on Stack Overflow

SQL - Adding a count to select clause

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related