SQL AverageIFS Equivalent for Query

Question

So I'm extremely new to using SQL. This syntax formatting is offensive but I am editing it in notepad and can't figure out a better alternative. I want it in the .dqy format but I don't know how else to edit. So if you have a suggestion please advise..

A general description: There is that SUM(CASE WHEN ...) portion of code below that I'm trying to get to work. I'm making an exception report, and I want another column that is the conditional average based on my concatenated value exclusive of that entry. IE, in Excel this is an easy formula (Column I):

=+IFERROR(AVERAGEIFS(H:H,C:C,[@Cust],A:A,"<>"&[@[Order Number]]),0)

Except I want to have it pull as part of the query. Here is a picture of the output:

Here is my SQL. Again I know formatting stinks if there is a different program than notepad that I can edit a .dqy in please advise. Also, what do those 3's mean in the 6th line of my code. I took a query from something else and have been modifying it into this project.

XLODBC
1
DRIVER=SQL Server;
SERVER=*OMIT*;
UID=*OMIT*;
Trusted_Connection=Yes;
APP=Microsoft Office 2010;
WSID=*OMIT*;
DATABASE=*OMIT*
SELECT DISTINCT RMORHP.ORHORDNUM AS 'Order Number',
                RMORHP.ORHCRTDTE AS 'Order Create Date',
                CONCAT(RMORHP.ORHCUSCHN, '-', RMORHP.ORHCUSNUM) AS 'Cust',
                RMORHP.ORHCUSCHN AS 'Chain ID',
                RMORHP.ORHCUSNUM AS 'Cust ID',
                RMCUSP.CUSCUSNAM AS 'Customer',
                RMORHP.ORHCRTUSR AS 'Created By',
                RMORHP.ORHORDQTY AS 'Units Ordered',
                SUM(CASE
                    WHEN RMCUSP.CUSCUSNAM = RMCUSP.CUSCUSNAM
                    THEN RMORHP.ORHORDQTY
                    END)
GROUP BY RMCUSP.CUSCUSNAM AS 'Total Units'
FROM BIDW_DataLake.erms.RMORHP RMORHP,
     BIDW_DataLake.eRMS.RMCUSP RMCUSP
WHERE (RMORHP.ORHCRTDTE BETWEEN ? AND ?)
    AND RMCUSP.CUSCUSCHN = RMORHP.ORHCUSCHN
    AND RMCUSP.CUSCUSNUM = RMORHP.ORHCUSNUM
    AND RMCUSP.CUSDFTDCN = 505
enter
    START date "yyyymmdd" enter END date "yyyymmdd"
3   3
Order Number    Create Date Cust    Chain ID    Cust ID Customer    Created By

Whenever I have SQL that isn't formatted nicely, I go here to fix it: https://sqlformat.org/ I have updated your question with the new format. — StoneGiant, Aug 10 '18 at 18:56
Thank you. Can I load that into my text file and it will work okay via msquery? — Matt Cottrill, Aug 11 '18 at 19:05
Yes, you should be able to paste the formatted SQL directly into MSQuery. — StoneGiant, Aug 13 '18 at 16:26

StoneGiant · Answer 1 · 2018-08-10T20:52:54.723

It looks like you are trying to get the average order quantity for other order a customer has made during a given date range. To say it another way: The average of a given customer's orders not including the order in the current row.

If that's correct, you might try replacing the SQL portion of your query with this:

SELECT R1.ORHORDNUM AS 'Order Number',
       R1.ORHCRTDTE AS 'Order Create Date',
       CONCAT(RMORHP.ORHCUSCHN, '-', RMORHP.ORHCUSNUM) AS 'Cust',
       R1.ORHCUSCHN AS 'Chain ID',
       R1.ORHCUSNUM AS 'Cust ID',
       RMCUSP.CUSCUSNAM AS 'Customer',
       R1.ORHCRTUSR AS 'Created By',
       R1.ORHORDQTY AS 'Units Ordered',
       (SELECT AVG(R2.HORDQTY)
          FROM BIDW_DataLake.erms.RMORHP R2
         WHERE R2.ORHCUSNUM = R1.ORHCUSNUM
           AND R2.ORHORDNUM <> R1.ORHORDNUM)  as 'Total Units'
FROM BIDW_DataLake.erms.RMORHP R1,
     BIDW_DataLake.eRMS.RMCUSP RMCUSP
WHERE (RMORHP.ORHCRTDTE BETWEEN ? AND ?)
    AND RMCUSP.CUSCUSCHN = R1.ORHCUSCHN
    AND RMCUSP.CUSCUSNUM = R1.ORHCUSNUM
    AND RMCUSP.CUSDFTDCN = 505

The Total Units column is what is known as a correlated subquery. Consider researching that.

What makes this a bad answer:

I have no way of testing it, and I'm not 100% certain of the MS-Query syntax where Excel connects with SQL Server.
MS-Query may not support correlated subqueries in this way.
I don't quite understand how the parameterized portion of the query works. That is, clearly it looks like the query is meant to ask for a start and end date and put those where the question marks go. I'm trusting that it works as-is. (Though, I suspect this is where your "3 3" is coming from.

So, probably vote this answer down and thrash me soundly for giving a bad answer, but maybe you will get something from it. I just thought it was too much to try to put in a comment.

You completely understood my point. Thank you for correctly formatting it. I will give this a go when I am in the office on Monday. While per your point (2) it may not work, but I really appreciate your reply. Also, in (3), the parameters work as intended, but i don't know what that 3 3 is coming from or what purpose it serves. It was in the original query that I'm modifying from. — Matt Cottrill, Aug 11 '18 at 19:07

Ary Jazz · Answer 2 · 2018-08-10T18:58:33.567

0

I din't totally understand the example but for a general case you can use:

AVG(CASE WHEN [condition1] and [condition2] THEN [the value you want to average] Else NULL END).

This shoud return the average if the column fir all the rows that meet your conditions.

edited Aug 10 '18 at 18:58

answered Aug 10 '18 at 18:53

Ary Jazz

1,576
1
16
25

SQL AverageIFS Equivalent for Query

2 Answers2