CODEWITHSUNDEEP
mysqlsql
SQL_New_bee
SQL_New_bee

Reputation: 125

Unable to multilply in SQL, aggregation in group by error

I have the following tables

create table table1(let_sub_area varchar(20), value int);

insert into table1 values('L1', 2500);
insert into table1 values('L2', 250);
insert into table1 values('L1', 500);
insert into table1 values('L2', 400);

create table table2(sub_area varchar(20), percentage float);
 
insert into table2 values('L1', 0.05);
insert into table2 values('L2', 0.10);

I write the following query

select t2.sub_area , (t1.total) * (t2.percentage) as percent_share
from table2 as t2
Join(
select let_sub_area, count(*) as total
from table1
group by let_sub_area
) as t1
On t1.let_sub_area=t2.sub_area
group by 1
order by 2

I want the out put as follows I want to take the totals for each category in table1 and multiply by percentage column in table 2:

sub_area|percetage share
  L1   | 150
  L2   | 65

I get an error which says that aggregation is not allowed! Please help

Upvotes: 0

Views: 55

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You seem to want join and group by:

select t2.let_sub_area, sum(t1.value * t2.percentage)
from table1 t1 join
     table2 t2
     on t1.let_sub_area = t2.sub_area
group by t2.let_sub_area;

No subqueries are needed. Your query is rather confusing, because it is not using value, but your results suggest that you want the percentage of the sum of value.

Upvotes: 0

ashkanyo
ashkanyo

Reputation: 132

You can use CTE.

        WITH cte
            AS (SELECT let_sub_area,
                   SUM(value) AS total
                FROM table1
                GROUP BY let_sub_area)
            SELECT cte.let_sub_area,
               cte.total * pct.percentage AS pctShare
            FROM cte
            INNER JOIN table2 AS pct
                ON cte.let_sub_area = pct.sub_area;

Upvotes: 0

Stu
Stu

Reputation: 32619

You can use a simple correlated subquery for thisL

select Sum(value) * (select percentage from table2 t2 where t2.sub_area=t1.let_sub_area) sub_area
from table1 t1
group by let_sub_area

Upvotes: 0

Rahul Biswas
Rahul Biswas

Reputation: 3467

First retrieving subcategory wise total (use sum instead of count) by using subquery then join with table2 for getting desired result. INNER JOIN is used as per given data. If any subarea is missing in subquery but results return for all subarea then use LEFT JOIN.

-- MySQL (v5.8)
SELECT t2.sub_area 
     , CAST(((t1.total) * (t2.percentage)) AS decimal) percent_share
FROM table2 t2
INNER JOIN (SELECT let_sub_area
                 , SUM(value) as total
            FROM table1
            GROUP BY let_sub_area
           ) t1
       ON t1.let_sub_area = t2.sub_area;

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=44c3efa5d87d5413fdfeb371c8d0234c

Upvotes: 1

Related Questions