Replace < 1 values in an aggregate SUM T-SQL query

Question

I am try to avoid using #temp to get an average where a field from table contains just a few less than 1 values. Convert to varchar, replace then convert back seems ugly. Suggestions very welcomed!!

Select
    (select cmp_zip from company where cmp_id = ord_originpoint) as OriginZip,
    ord_originpoint as OriginPoint,
    replace((select cty_nmstct from company where cmp_id = ord_originpoint),'/','') as Origin_City_State,
    (select cmp_zip from company where cmp_id = ord_destpoint) as DestZip,
    ord_destpoint as DestPoint,
    replace((select cty_nmstct from company where cmp_id = ord_destpoint),'/','') as Dest_City_State,
    COUNT(ord_hdrnumber) as OrdCount,
    SUM(ord_rate)/COUNT(ord_hdrnumber) as AvgRate,
    SUM(ord_totalmiles)/COUNT(ord_hdrnumber) as AvgMiles,
    (SUM(ord_rate) / COUNT(ord_hdrnumber)) / (SUM(ord_totalmiles) / COUNT(ord_hdrnumber)) as AvgRevperMiles
from 
    orderheader (NOLOCK) 
where ord_billto <> 'CSXJAC01' 
  and ord_revtype1 = 'NE' 
  and ord_status = 'CMP' 
  and ord_bookdate > GETDATE() - 730 
  and ord_completiondate < GETDATE()
  and ord_totalmiles > 0 
group by 
    ord_originpoint,ord_destpoint 
order by 
    OrdCount desc

If I exclude columns less than zero in the totalmiles column I get :

+-------+----------+---------------+-------+-------+----------+-----------------+-------+---+------+-----+
|  8850 | DSDMIL   | MILLTOWN      | NJMID |  7206 | NORELI   | ELIZABETH       | NJ    | 1 |  243 |  25 |
|  7047 | CSXNOR   | NORTH BERGEN  | NJ    | 11550 | NASHEM   | HEMPSTEAD       | NYNAS | 1 |  492 |  34 |
|  7047 | CSXNOR   | NORTH BERGEN  | NJ    |  7022 | PERFAI   | FAIRVIEW        | NJBER | 1 |  190 |   1 |
| 17013 | PEPCAR01 | CARLISLE      | PA    | 21224 | CSXBAL   | BALTIMORE       | MD    | 1 |  350 |  97 |
| 23944 | GARKEN   | KENBRIDGE     | VA    | 21224 | CSXBAL   | BALTIMORE       | MD    | 1 |  814 | 230 |
| 21224 | CSXBAL   | BALTIMORE     | MD    | 18202 | HAZHAZ04 | HAZLETON        | PA    | 1 |  621 | 161 |
| 17055 | WOOMEC   | MECHANICSBURG | PACUM | 21224 | CSXBAL   | BALTIMORE       | MD    | 1 |  355 |  85 |
| 23139 | MORPOW01 | POWHATAN      | VA    | 21224 | CSXBAL   | BALTIMORE       | MD    | 1 | 1376 | 186 |
| 17109 | PEPHAR   | HARRISBURG    | PA    | 21224 | CSXBAL   | BALTIMORE       | MD    | 1 |  350 |  78 |
| 21224 | CSXBAL   | BALTIMORE     | MD    | 20066 | WASWAS05 | WASHINGTON      | DC    | 1 |  675 |  54 |
| 21224 | CSXBAL   | BALTIMORE     | MD    | 20743 | GRACAP   | CAPITOL HEIGHTS | MD    | 1 |  300 |  45 |
|  7047 | CSXNOR   | NORTH BERGEN  | NJ    |  7866 | ROCROC05 | ROCKAWAY        | NJ    | 1 |  243 |  34 |
| 21224 | CSXBAL   | BALTIMORE     | MD    | 20772 | BOBUPP   | UPPER MARLBORO  | MD    | 1 |  283 |  37 |
+-------+----------+---------------+-------+-------+----------+-----------------+-------+---+------+-----+

Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Sep 20 '17 at 15:03
for speed. I get these ad hoc requests from the powers and looking to respond faster and thanks — BillB, Sep 20 '17 at 15:05
`contains just a few less than 1 values` so you mean 0? I just dont understant what is the question here :( — Juan Carlos Oropeza, Sep 20 '17 at 15:06
It's not clear to me what you're actually trying to do here, and we're missing a bunch of information (is this all from one table?) — Rich Benner, Sep 20 '17 at 15:06
Performance questions should include `EXPLAIN ANALYZE` and some information about table size, index, current time performance, desire time, etc. `Slow` is a relative term and we need a real value to compare. [**How do I obtain a Query Execution Plan?**](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) — Juan Carlos Oropeza, Sep 20 '17 at 15:07
i *think* you want to look at using aggregated CASE expressions in your SELECT statement, but I'm still not entirely sure what you're asking — Rich Benner, Sep 20 '17 at 15:10
@BillB you have to remember that we have no idea what this query is or what it's supposed to do. You need to make sure you're giving us enough context to actually answer your question. Solid facts like what you're exactly trying to do, what the tables/data look like, also remove anything that doesn't relate to this exact question, see here https://stackoverflow.com/help/mcve — Rich Benner, Sep 20 '17 at 15:13
Sorry was just looking for a better way to replace a less than 1 value in a field in an aggregate query to prevent division by zero. I can do it with a temp table — BillB, Sep 20 '17 at 15:15
`a less than 1 value in a field ` what field ? Show us source data, current output and desire output. Also you can format your table here https://senseful.github.io/text-table/ — Juan Carlos Oropeza, Sep 20 '17 at 15:17

score 0 · Answer 1 · answered Sep 20 '17 at 15:23

I'm still not entirely sure of the exact question that you're asking, but the way that you're doing subqueries within your SELECT statement is always going to be poor for performance, try using correct joins, something like this;

SELECT
    c1.cmp_zip AS OriginZip
    ,oh.ord_originpoint AS OriginPoint
    ,replace(c1.cty_nmstct),'/','') AS Origin_City_State
    ,c2.cmp_zip AS DestZip
    ,oh.ord_destpoint AS DestPoint
    ,replace(c2.cty_nmstct,'/','') AS Dest_City_State
    ,COUNT(oh.ord_hdrnumber) AS OrdCount
    ,SUM(oh.ord_rate)/COUNT(oh.ord_hdrnumber) AS AvgRate
    ,SUM(oh.ord_totalmiles)/COUNT(oh.ord_hdrnumber) AS AvgMiles
    ,(SUM(oh.rd_rate)/COUNT(oh.ord_hdrnumber))/( SUM(oh.ord_totalmiles)/COUNT(oh.ord_hdrnumber)) AS AvgRevperMiles
FROM orderheader oh
LEFT JOIN company c1
    ON oh.ord_originpoint = c1.cmp_id
LEFT JOIN company c2
    ON oh.ord_destpoint = c2.cmp_id 
WHERE oh.ord_billto <> 'CSXJAC01' 
    AND oh.ord_revtype1 = 'NE' 
    AND oh.ord_status = 'CMP' 
    AND oh.ord_bookdate > GETDATE() - 730 
    AND oh.ord_completiondate < GETDATE()
    AND oh.ord_totalmiles > 0 
GROUP BY 
    c1.cmp_zip
    ,oh.ord_originpoint
    ,replace(c1.cty_nmstct),'/','')
    ,c2.cmp_zip
    ,oh.ord_destpoint
    ,replace(c2.cty_nmstct,'/','')
ORDER BY OrdCount DESC

You may need to tweak the odd field (i'm not sure the table that contains the field rd_rate for example, althouth this may be a typo for ord_rate at a guess)

Replace < 1 values in an aggregate SUM T-SQL query

1 Answers1