How to optimize sql select statement?

Question

I am having an issue with SQL select statement. I am trying to get the percentage using below logic.

For example, I have two tables. One is TableA and another TableB

TableA has column ID, A1, A2.., Get total distinct count of A1 as "X".
TableB has column ID, B1, B2, FK_A1. Get count of B2 as "Y".
Get (Y/X)*100 as Total Percentage.

I was able to do it using subqueries but would like to use a simple and effective statement. Is it possible to get all the above 3 cases in one select statement? Your help would be highly appreciated.

Select 
     (Select count(distinct A1)  from TableA) As C1, 
     (Select count(B2) from TableB Inner Join TableA ON TableB.FK_A1=TableA.A1) 
C2)

please add query-optimization tag to your question

Imran Ali
– Imran Ali

2019-08-27 05:01:14 +00:00
Commented Aug 27, 2019 at 5:01 — Imran Ali
– Imran Ali, Commented Aug 27, 2019 at 5:01

Lets-c-codeigniter · Accepted Answer · 2019-08-27 05:08:43Z

1

Try this query

SELECT ( COUNT(B2) / COUNT(DISTINCT A1) ) * 100 AS TOTAL_PERC FROM TABLEA A INNER JOIN TABLEB B ON TABLEB.FK_A1 = TABLEA.A1;

answered Aug 27, 2019 at 5:08

Lets-c-codeigniter

7142 gold badges7 silver badges19 bronze badges

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

Comments

Popeye · Accepted Answer · 2019-08-27 05:04:40Z

0

You can use a simple join between two tables:

SELECT
    ( COUNT(DISTINCT A1) / COUNT(B2) ) * 100 AS PRCNTG
FROM
    TABLEA A
    INNER JOIN TABLEB B ON TABLEB.FK_A1 = TABLEA.A1;

apply DISTINCT on B2 if needed in your case.
apply OUTER join if needed in your case.

Cheers!!

answered Aug 27, 2019 at 5:04

Popeye

36k4 gold badges12 silver badges31 bronze badges

1 Comment

Popeye Over a year ago

If any of the answer solved your question then please accept it, so that your question is marked as resolved.

DarkRob · Accepted Answer · 2019-08-27 05:16:42Z

0

You can use inner join with group by to achieve this. You can also use cte to make your distinct records separately.

; with cte as (
select A1, Count(distinct A1) as CountA from tableA)
, ct as (
select distinct FK_A1 , count(b2) as Count from tableB)
select 100 * ct.count/cte.CountA as totalpercentage 
from cte 
     inner join ct 
     on cte.A1=ct.FK_A1

answered Aug 27, 2019 at 5:16

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to optimize sql select statement?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related