• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

MySQL > Syntax to create a sum of all column counts

Using MySQL.  I have a query that shows our top 10 products.  It works fine.  

What I'm having trouble with is deriving the percentage that each product represents of those top 10.  I need to somehow ignore the Group by clause for that one field  

My data set that I get back from my working query looks like this:


Product                              ProductCount
The #1 product name      229
The #2 product name      140
The #3 product name      125
The #4 product name      95
The #5 product name      89
The #6 product name      87
The #7 product name      86
The #8 product name      50
The #9 product name      34
The #10 product name      31

What i want my data set to look like is this.  I don't care if SumOfProductCount is displayed or not, but obviously, it is necessary to calculate it to get ProductPercentage to come out:

Product                              ProductCount      SumOfProductCount      ProductPercentage
The #1 product name      229                      966                                      0.24
The #2 product name      140                      966                                      0.14
The #3 product name      125                      966                                      0.13
The #4 product name      95                      966                                0.10
The #5 product name      89                      966                                       0.09
The #6 product name      87                      966                                       0.09
The #7 product name      86                      966                                      0.09
The #8 product name      50                      966                                      0.05
The #9 product name      34                      966                                      0.04
The #10 product name      31                      966                                      0.03

Here is my working SQL:

	select product, count(product) as productcount from 
		(select date_format(t.startdate, '%Y-%m') as reportmonth, if(isnull(dx.translateval), t.product, dx.translateval) as product
		from t_transcripts t

		left join t_producttranslate dx 
		on t.product = dx.inputval

		where account = '12345678' 
		and skill = 'theskillname' 
		and not isnull(product) 
		and not isnull(conversion)
		and year(t.startdate) = year(current_date)
		and month(t.startdate) = month(current_date)) t1
	group by product
	order by productcount desc
	limit 10

Open in new window


Here is my non-working SQL, but you can see what i'm trying to do.  It only gives one row back, instead of 10:

select product, productcount, sum(productcount) from 
	(select product, count(product) as productcount from 
		(select date_format(t.startdate, '%Y-%m') as reportmonth, if(isnull(dx.translateval), t.product, dx.translateval) as product
		from t_transcripts t

		left join t_producttranslate dx 
		on t.product = dx.inputval

		where account = '12345678' 
		and skill = 'theskillname' 
		and not isnull(product) 
		and not isnull(conversion)
		and year(t.startdate) = year(current_date)
		and month(t.startdate) = month(current_date)) t1
	group by product
	order by productcount desc
	limit 10)t2

Open in new window

0
ducky801
Asked:
ducky801
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should work better, doing it in 2 steps:

first create a view like this:
create view ProductCountsThisMonth as
select product, count(product) as productcount from 
		(select date_format(t.startdate, '%Y-%m') as reportmonth, if(isnull(dx.translateval), t.product, dx.translateval) as product
		from t_transcripts t

		left join t_producttranslate dx 
		on t.product = dx.inputval

		where account = '12345678' 
		and skill = 'theskillname' 
		and not isnull(product) 
		and not isnull(conversion)
		and year(t.startdate) = year(current_date)
		and month(t.startdate) = month(current_date)) t1
	group by product
[code]

next step will be easy:
[code]
select m.product, m.productcount 
, x.total_count
, m.productcount / x.total_count
from ProductCountsThisMonth m
,( select sum(productcount) total_count
    from ProductCountsThisMonth
     ) x
order by m.productcount  desc
limit 10

Open in new window

0
 
HainKurtSr. System AnalystCommented:
try

select s.*, (s.ProductCount/sa.total_sum)*100 as percent_sum from (
select product, count(product) as productcount from 
		(select date_format(t.startdate, '%Y-%m') as reportmonth, if(isnull(dx.translateval), t.product, dx.translateval) as product
		from t_transcripts t 
		left join t_producttranslate dx 
		on t.product = dx.inputval
		where account = '12345678' 
		and skill = 'theskillname' 
		and not isnull(product) 
		and not isnull(conversion)
		and year(t.startdate) = year(current_date)
		and month(t.startdate) = month(current_date)) t1
	group by product
	order by productcount desc
) s, (select sum(productcount) total_sum from (select productcount from t_transcripts limit 10)) sa

Open in new window

0
 
ducky801Author Commented:
Thanks angelIII.  You and I think alike.

I had tried to create a view earlier, but was unsuccessful.  I got this error back when I made the attempt (Using MySQL Workbench 5.2.41 CE / MySQL Server 5.5.27 CE):

ERROR 1349: View's SELECT contains a subquery in the FROM clause.  

Any other ideas?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
HainKurtSr. System AnalystCommented:
i am thinking something like this

select s.*, (s.ProductCount/sa.total_sum)*100 as percent_sum from (
select product, count(product) as productcount from 
		(select date_format(t.startdate, '%Y-%m') as reportmonth, if(isnull(dx.translateval), t.product, dx.translateval) as product
		from t_transcripts t 
		left join t_producttranslate dx 
		on t.product = dx.inputval
		where account = '12345678' 
		and skill = 'theskillname' 
		and not isnull(product) 
		and not isnull(conversion)
		and year(t.startdate) = year(current_date)
		and month(t.startdate) = month(current_date)) t1
	group by product
	order by productcount desc
	limit 10
) s, (select sum(cpc) total_sum from (select count(productcount) cpc from t_transcripts group by product order by productcount desc limit 10)) sa

Open in new window


oops, we need to put same conditions to second query as well, which is not good... simply what you need is this

select s.*, (s.ProductCount/sa.SumOfProductCount)*100 as ProductPercentage 
  from (org query here which gives you product, productcount) s, 
       (select sum(productcount) SumOfProductCount from (org query here which gives you product, productcount)) sa

Open in new window

0
 
hnasrCommented:
Compare with this: table:tbl (Model, name, ....) : just replace tbl and model with your values.

select model, (select count(*) from tbl b where b.model=tbl.model) as Count, 
 (select count(*) from tbl b where b.model=tbl.model)/(select count(*) from tbl) as Ratio
from tbl
group by model;

Open in new window

0
 
joaoalmeidaCommented:
Try:

select product, productcount, sum(productcount) from 
	(select product, count(product) as productcount from 
		(select date_format(t.startdate, '%Y-%m') as reportmonth, if(isnull(dx.translateval), t.product, dx.translateval) as product
		from t_transcripts t

		left join t_producttranslate dx 
		on t.product = dx.inputval

		where account = '12345678' 
		and skill = 'theskillname' 
		and not isnull(product) 
		and not isnull(conversion)
		and year(t.startdate) = year(current_date)
		and month(t.startdate) = month(current_date)) t1
	group by product
	order by productcount desc
	limit 10)t2
    group by product, productcount
    order by productcount desc 

Open in new window

0
 
ducky801Author Commented:
Thanks for the help all.  HainKurt's solution is probably the best.  I wish there was a sexier way, but I think his option is probably the only one if one wanted to use pure SQL.  Throwing a few points to AngelIII  too because I like the concept, but couldn't implement because MySQL didn't like the nesting.  For future readers: I didn't end up using either of these solutions because this query was part of a bigger transformation I was doing in PDI (Pentaho Kettle).  My ultimate solution was to use my original, working query from this thread and derive a "sum of the counts" by using the "Group By" step in PDI.  (and within that step - not actually defining anything to group by, yields a "sum of the counts")
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now