SQL Order By With Calculations

[AaronWard]

I am trying to import data from my database. My current SQL is as follows:

SELECT DISTINCT (rep.lastname+', '+rep.firstname) AS Rep, 
(SELECT COUNT(*) FROM LiveData WHERE WorkingRep = tli.WorkingRep) AS Worked, 
(SELECT COUNT(*) FROM LiveData WHERE SuccessType = 'PTP' AND WorkingRep = tli.WorkingRep) AS PTP, 
ROUND(((SELECT COUNT(*) FROM LiveData WHERE SuccessType = 'PTP' AND WorkingRep = tli.WorkingRep) / (SELECT COUNT(*) FROM LiveData WHERE WorkingRep = tli.WorkingRep)), 2) * 100 AS [% PTP], 
ROUND(((SELECT COUNT(*) FROM LiveData WHERE SuccessType = 'PYMT' AND WorkingRep = tli.WorkingRep) / (SELECT COUNT(*) FROM LiveData WHERE WorkingRep = tli.WorkingRep)), 2) * 100 AS [% PYMT]
FROM tblrep_ref AS rep INNER JOIN LiveData AS tli ON rep.VZID=tli.WorkingRep;

This obviously takes FOREVER to pull data due to the number of queries being made. I have started a revision, however I am unable to complete it. I am wanting to have the ability to sort by [% PTP] or even [% PYMT]

 SELECT (rep.LastName+', '+rep.FirstName) as [Rep Name], Count(*) as Worked
FROM tblrep_ref AS rep INNER JOIN LiveData AS tli ON rep.VZID=tli.WorkingRep
Where WorkingRep IS NOT NULL
GROUP BY (rep.LastName+', '+rep.FirstName);

However, I am still unsure how to calculate my required fields this way...any thoughts?

 

[vbaInet]

Look back at your query, there seems to be select statements that are repeated twice or more. Get rid of the duplicate Select statements and use the alias field for calculation.

That should speed things up a bit.

 

[AaronWard]

Thank you for your response! It does indeed speed things up a bit. However, what I'm looking for is the ability to sort my specific columns of data. Here is my 'simulated' sql statement. Of course tho, it does not work, I am unsure as to how to get it to work. Any thoughts?

SELECT 
(rep.lastname + ', ' + rep.firstname) AS Rep,
COUNT(*) AS TotalWorked,
COUNT(Action = 'PTP' OR 'Payment') as [Customers Saved],
ROUND(([Customers Saved] / TotalWorked) * 100), 2) AS [% Customers Saved]
 
FROM tblrep_ref AS rep INNER JOIN LiveData AS tli ON rep.VZID=tli.WorkingRep
 
WHERE 
WorkingRep IS NOT NULL 
AND
[Language Ind]<>1
 
GROUP BY  (rep.lastname + ', ' + rep.firstname)
ORDER BY [% Customers Saved]

 

[vbaInet]

WHERE WorkingRep [B][COLOR=Red]Not [/COLOR]Is Null[/B]

ORDER BY ROUND(([Customers Saved] / TotalWorked) * 100), 2)

Or best you create another query based on this one and sort using the alias.

 

[AaronWard]

Fantastic work - thanks guys! I have all my data queried perfectly! I am, however, unable to sort by [% Customers Saved] field. I have tried ORDER BY the alias as well as my ROUND calculation formula. Neither are working. Heres what I have that DOES work:

SELECT 
(rep.lastname+', '+rep.firstname) AS Rep, 
COUNT(*) AS Worked, (SELECT COUNT(*) FROM LiveData WHERE LEFT(Action,8) = 'OUTBOUND' AND WorkingRep =tli.WorkingRep) AS [Customers Saved], 
FORMAT(Round((([Customers Saved]/Worked)*100),1),'0#.0') & '%' AS [% Customers Saved]
FROM tblrep_ref AS rep INNER JOIN LiveData AS tli ON rep.VZID=tli.WorkingRep
WHERE WorkingRep Is Not Null And [Language Ind]<>1
GROUP BY (rep.lastname+', '+rep.firstname), WorkingRep;

The methods of ordering I have tried, without success, are as follows:

ORDER BY [% Customers Saved]
ORDER BY Round((([Customers Saved]/Worked)*100),1)

any thoughts?

 

[vbaInet]

Use my second suggestion, create a second query and then you will be able to sort using [% Customers Saved]

 

[AaronWard]

Use my second suggestion, create a second query and then you will be able to sort using [% Customers Saved]

Im sorry, I do not quite understand what you mean by this. My sql comprehension is moderate at best, but growing. Would you care to eleborate a bit on using the second query? Thank you!

 

[vbaInet]

Create a second and include all the fields from your main query. Go into design view of this second query and apply the sort.

 

[AaronWard]

Create a second and include all the fields from your main query. Go into design view of this second query and apply the sort.

PERFECT! Thank you SO VERY MUCH for this enlightening information! Works pefectly!

 

[vbaInet]

Excellent!! Glad that worked for you.