CODEWITHSUNDEEP
mysqlsql
DV93
DV93

Reputation: 23

how to have a column values as a variable in the where condition in mysql

I have a table named Transactions which has columns transaction_id, prod_subcat_code and few more. In the where clause I have given two conditions. But I am not getting the desired result.

Select prod_subcat_code, 
(select count(transaction_id) from Transactions where Qty>'0' and 
prod_subcat_code='1')*100/(select count(transaction_id) from Transactions) 
as Sales_Percentage,
(select count(transaction_id) from Transactions where Qty<'0' and 
prod_subcat_code ='1')*100/(select count(transaction_id) from Transactions) 
as Returns_Percentage from Transactions group by prod_subcat_code;
+------------------+------------------+--------------------+
| prod_subcat_code | Sales_Percentage | Returns_Percentage |
+------------------+------------------+--------------------+
|                1 |          11.5063 |             1.2881 |
|                2 |          11.5063 |             1.2881 |
|                3 |          11.5063 |             1.2881 |
|                4 |          11.5063 |             1.2881 |
|                5 |          11.5063 |             1.2881 |
|                6 |          11.5063 |             1.2881 |
|                7 |          11.5063 |             1.2881 |
|                8 |          11.5063 |             1.2881 |
|                9 |          11.5063 |             1.2881 |
|               10 |          11.5063 |             1.2881 |
|               11 |          11.5063 |             1.2881 |
|               12 |          11.5063 |             1.2881 |
+------------------+------------------+--------------------+
12 rows in set (0.04 sec)

I want to be able to find sales and returns percentage for each prod_subcat_code, for that I want some way through which I could give prod_subcat_code as a variable in the where clause so that it could take the same value as the column prod_subcat_code has. As of now I have given a value which is 1 and thus showing the right result for the first row but the next rows are also getting the same value. Is there a way to do the row wise update, so that prod_subcat_code increments by 1 value with each row.

Upvotes: 0

Views: 40

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

To answer your question, you need to use a table alias for the outer query. Then the subqueries can reference that table alias, and become correlated subqueries.

SELECT prod_subcat_code, 
 (SELECT COUNT(transaction_id) FROM Transactions 
  WHERE Qty>'0' AND prod_subcat_code=t.prod_subcat_code) * 100 / 
 (SELECT COUNT(transaction_id) FROM Transactions) 
  AS Sales_Percentage,
 (SELECT COUNT(transaction_id) FROM Transactions 
  WHERE Qty<'0' AND prod_subcat_code=t.prod_subcat_code) * 100 / 
 (SELECT COUNT(transaction_id) FROM Transactions) 
  AS Returns_Percentage 
FROM Transactions AS t
GROUP BY t.prod_subcat_code;

But it can be simpler than that. If you just use expressions instead of subqueries, you don't need to have any WHERE clause restricting the set of rows to a matching group. They're already restricted to the group by virtue of the GROUP BY.

Select prod_subcat_code, 
 SUM(CASE WHEN Qty>0 THEN 1 END) * 100 / (SELECT COUNT(*) FROM Transactions) AS Sales_Percentage,
 SUM(CASE WHEN Qty<0 THEN 1 END) * 100 / (SELECT COUNT(*) FROM Transactions) AS Returns_Percentage
FROM Transactions
GROUP BY prod_subcat_code;

Upvotes: 1

Related Questions