MS SQL Confusion in multi level select query

Question

I have a MS SQL table with 9 variables in it:

  Table (var1, var2, var3 ... var9)

I wrote the following query:

  SELECT var1, COUNT(var10)
  FROM (
     SELECT *, (CASE WHEN ABS(var1 - var2) >= 1 THEN 1 ELSE 0 END) as var10
        FROM DB.Table_Name
        GROUP BY var 3
        ORDER BY var 3
       )

I am getting errors:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I don't know what I am doing wrong here...

What I want is:

Add one more variable Var10 in the table, based upon the condition:

 (CASE WHEN ABS(var1 - var2) >= 1 THEN 1 ELSE 0 END)

Then calculate:

The counts of Var10 Grouped By Var3 and in descending order

Could someone please help?

Sample Data:

Var1    Var2    Var3    Var4    Var5    Var6    Var7    Var8    Var9

2   1   ABC123BCUSUS130356  69  513008  32:13.0 32:26.3 95662   01/01/2016 6:27:44  
2   1   ABC123BCUSUS130351  69  513013  38:29.6 38:41.9 95684   01/02/2016 6:27:44  
3   2   ABC123BCUSUS130356  69  513036  20:05.6 20:19.7 95800   01/02/2016 6:27:44  
2   1   ABC123BCUSUS130352  69  513041  30:51.1 31:04.0 95829   01/03/2016 6:27:44  
2   4   ABC123BCUSUS130356  69  513047  45:02.6 45:12.2 95855   01/01/2016 6:27:44  
2   1   ABC123BCUSUS130356  69  513048  47:46.5 47:59.3 95863   01/01/2016 6:27:44  
3   1   ABC123BCUSUS130353  69  513052  51:13.5 51:27.4 95879   01/02/2016 6:27:44  
2   5   ABC123BCUSUS130356  69  513064  06:46.7 06:47.5 95945   01/01/2016 6:27:44  
2   1   ABC123BCUSUS130356  69  513069  18:49.4 19:03.0 95971   01/03/2016 6:27:44  
2   2   ABC123BCUSUS130354  69  513077  43:21.0 43:34.9 96008   01/01/2016 6:27:44  
2   1   ABC123BCUSUS130356  69  513079  46:12.1 46:12.6 96021   01/02/2016 6:27:44  
4   1   ABC123BCUSUS130352  69  513084  53:00.1 53:13.9 96048   01/01/2016 6:27:44  
2   1   ABC123BCUSUS130356  69  513091  01:28.4 01:40.7 96081   01/01/2016 6:27:54  
2   1   ABC123BCUSUS130353  69  513104  32:54.4 33:19.3 96138   01/03/2016 6:27:54  
5   3   ABC123BCUSUS130356  69  513108  36:09.5 36:23.1 96159   01/01/2016 6:27:54  
2   1   ABC123BCUSUS130356  69  513121  03:09.0 03:21.7 96228   01/01/2016 6:27:54  
2   1   ABC123BCUSUS130352  69  513123  09:03.6 09:17.4 96242   01/02/2016 6:27:54  
5   1   ABC123BCUSUS130356  69  513127  11:33.0 11:45.9 96260   01/01/2016 6:27:54  
2   4   ABC123BCUSUS130352  69  513138  33:01.5 33:14.8 96315   01/03/2016 6:27:54  
3   1   ABC123BCUSUS130356  69  513147  44:40.7 44:59.5 96355   01/01/2016 6:27:54  
1   4   ABC123BCUSUS130356  69  513149  53:04.2 53:04.6 96368   01/01/2016 6:27:54  
2   1   ABC123BCUSUS130356  69  513152  00:00.5 00:13.3 96386   01/01/2016 6:27:54

Suppose this is the sample query for creating Var10 :

Var3                Var10
ABC123BCUSUS130351  1
ABC123BCUSUS130352  1
ABC123BCUSUS130353  1
ABC123BCUSUS130353  1
ABC123BCUSUS130356  2
ABC123BCUSUS130352  1
ABC123BCUSUS130355  2
ABC123BCUSUS130354  3
ABC123BCUSUS130356  1
ABC123BCUSUS130356  0
ABC123BCUSUS130354  1
ABC123BCUSUS130355  3
ABC123BCUSUS130351  1
ABC123BCUSUS130352  1
ABC123BCUSUS130352  2
ABC123BCUSUS130356  1
ABC123BCUSUS130356  1
ABC123BCUSUS130356  4
ABC123BCUSUS130356  2
ABC123BCUSUS130356  2
ABC123BCUSUS130356  3
ABC123BCUSUS130356  1

Then Final output should look like:

Var3                Count(Var10)
ABC123BCUSUS130356    10
ABC123BCUSUS130352    5
ABC123BCUSUS130355    5
ABC123BCUSUS130354    4
ABC123BCUSUS130351    2
ABC123BCUSUS130353    2

To use *Order By* clause you need to use *TOP* or *OFFSET* or *FOR XML*, you can modify your query like `SELECT yourColumnProperties FROM ( SELECT TOP (yourValue) yourColumnProperties FROM DB.Table_Name GROUP BY var3 ORDER BY var3)` — Mahedi Sabuj, Aug 22 '16 at 07:27
Related http://stackoverflow.com/questions/18031421/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu — DVJex, Aug 22 '16 at 07:28
@DVJex , my work is different from the one you referred. I am creating Var10 using some conditions (as mentioned above) and then counting Var10 group by existing Var3 in the database table. — Madhu Sareen, Aug 22 '16 at 07:38
Please post some sample data and tell us what would be your expected output — StackUser, Aug 22 '16 at 07:55
What do you want? It is not clear. Do you want `var1 count` in table? — neer, Aug 22 '16 at 08:31
@NEER I have added the questions to help in better understanding. — Madhu Sareen, Aug 22 '16 at 08:35
Sample data is good. Sample data and *expected results* would be better (as in, in tabular form, rather than just relying on your narrative) — Damien_The_Unbeliever, Aug 22 '16 at 08:39

score 1 · Accepted Answer · answered Aug 22 '16 at 08:46

1

If I understood you requirement correctly the you may looking for this

SELECT var3, SUM(var10)
FROM (
    SELECT var3,
           (CASE WHEN ABS(var1 - var2) >= 1 THEN 1 ELSE 0 END) as var10
    FROM DB.Table_Name
)M
GROUP BY var3
ORDER BY var3

answered Aug 22 '16 at 08:46

Jaydip Jadhav

12,179
6
24
40

1

Worked...!! Thanks @JayDipJ I was almost correct except that 'M' at the end. – Madhu Sareen Aug 22 '16 at 08:50

score 1 · Answer 2 · answered Aug 22 '16 at 08:53

1

You don't need a subquery at all. I believe you intend:

SELECT var3,
       SUM(CASE WHEN ABS(var1 - var2) >= 1 THEN 1 ELSE 0 END) as var10
FROM DB.Table_Name
GROUP BY var3
ORDER BY var3;

count on var10 doesn't make sense or produce the results you really want.

answered Aug 22 '16 at 08:53

Gordon Linoff

1,242,037
58
646
786

Thanks @Gordon Linoff , I needed sub-query because I am doing some more things as well. I cant show these over here due to some reasons. – Madhu Sareen Aug 22 '16 at 08:57

MS SQL Confusion in multi level select query

2 Answers2