Click here to Skip to main content
15,867,870 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have below mnetioned query which servers my requiremnt, but i want to avoid union all so i need to mention table once & other condition only one time.

Is there any other way to write this query.

Thanks in advance

What I have tried:

SELECT 'UPTO 1 LAKH' AS 'Cretiria',COUNT(*) as Account ,SUM(LimitAmt) as Limit FROM LON20190629 where [LimitAmt] between 0 and 100000 and AcOpDt>=20190401
UNION ALL
SELECT 'ABOVE 1 AND UPTO 5' AS 'Cretiria', COUNT(*),SUM(LimitAmt)  FROM LON20190629 where [LimitAmt] between 100001  and 500000 and AcOpDt>=20190401
UNION ALL
SELECT 'ABOVE 5 AND UPTO 10' AS 'Cretiria', COUNT(*),SUM(LimitAmt)  FROM LON20190629 where [LimitAmt] between 500001 AND 1000000 and AcOpDt>=20190401
UNION ALL
SELECT 'ABOVE 10 AND UPTO 25' AS 'Cretiria', COUNT(*),SUM(LimitAmt)  FROM LON20190629 where [LimitAmt] between 1000001 AND 2500000 and AcOpDt>=20190401
UNION ALL
SELECT 'ABOVE 25 AND UPTO 1CR' AS 'Cretiria', COUNT(*),SUM(LimitAmt)  FROM LON20190629 where [LimitAmt] between 2500000 AND 10000000 and AcOpDt>=20190401
UNION ALL
SELECT 'ABOVE 1CR' AS 'Cretiria', COUNT(*),SUM(LimitAmt)  FROM LON20190629 where [LimitAmt]>10000000 and AcOpDt>=20190401
Posted
Updated 1-Sep-20 8:59am

1 solution

First of all, why you want to rewrite this? If the query returns correct results and performs fast enough what's the benefit?

If you're worried about the execution plan, choosing the optimal plan is the job optimizer does and it quite often does a good job as long as proper access paths are available.

Having that said, this can be re-written in several ways. For example one variation could be something like
SQL
with innerquery (Criteria, LimitAmt) AS (
   SELECT CASE
          WHEN [LimitAmt] between 0       and 100000 THEN 'UPTO 1 LAKH' 
          WHEN [LimitAmt] between 100001  and 500000 THEN 'ABOVE 1 AND UPTO 5'
          WHEN [LimitAmt] between 500001 AND 1000000 THEN 'ABOVE 5 AND UPTO 10'
		  ...
		  WHEN [LimitAmt] > 10000000 THEN 'ABOVE 1CR'
	   END AS Criteria,
       LimitAmt
   FROM LON20190629 
   WHERE AcOpDt>=20190401
)
SELECT iq.Criteria,
       COUNT(*) as Account,
	   SUM(iq.LimitAmt) as Limit
FROM innerquery iq
GROUP BY iq.Criteria;
 
Share this answer
 
Comments
Maciej Los 1-Sep-20 17:13pm    
5ed!
Wendelius 3-Sep-20 11:38am    
Thank you!
Espen Harlinn 1-Sep-20 19:56pm    
Hi Mika, nice to see that you are still around, btw. nice answer :-)
Wendelius 3-Sep-20 11:39am    
Hello Espen and glad to hear from you! Long time no see :)
Espen Harlinn 3-Sep-20 20:23pm    
Well, I'm back ... I've written a couple of articles about ESE, the no sql database engine included with Windows.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900