trouble in SQL join two SELECT statement

Question

I am calculating the quantile of the desired record in a table. There are only two columns in the table, X1 <- date, X2 <- price

I use the first select statement to count the number of records less than the one at desired date; the second select statement to count the total record number

Each SELECT statement runs good, but I have trouble to JOIN them together, any hint?

(

SELECT count(X2) AS rank

FROM EMCDX5y_test

WHERE CONVERT(decimal(8,3), X2) < (SELECT X2 as Current_Level

FROM EMCDX5y_test

WHERE X1 =(SELECT max(X1) from EMCDX5y_test ) )

)

as r

INNER JOIN

(

SELECT count(X2) AS total

FROM EMCDX5y_test

) as t

#

here is the error message

Server: Msg 156, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'as'.

Server: Msg 156, Level 15, State 1, Line 13

Incorrect syntax near the keyword 'as'.

you don't have a join condition, e.g. `INNER JOIN foo ON bar`... how is the DB supposed to know how to relate these two "tables"? — Marc B, Sep 26 '13 at 17:32
i tried "on 1 = 1" since the result of each statement only has one numeric, there is actually no specific condition. But it did not work. — simeonyyyyyy, Sep 26 '13 at 17:41
You probably want a UNION query. joins are supposed to be between related data, but you're just trying to slurp out two totally different values. any reason you HAVE to do this as a query? Regardless of how you structure things, the DB will STILL have run at least 2 separate queries anyways. — Marc B, Sep 26 '13 at 17:49

duffymo · Answer 1 · 2013-09-26T17:40:53.343

1

Usually written this way:

SELECT *
FROM X
JOIN Y
ON X.key = Y.key

I don't see the keys or the ON clause.

I wonder if you really need a GROUP BY. It sounds like you want to put values into buckets. I don't think you're even close with what you have.

Maybe this is more of what you want:

http://technet.microsoft.com/en-us/library/ms175126.aspx

edited Sep 26 '13 at 17:40

answered Sep 26 '13 at 17:34

duffymo

305,152
44
369
561

score 1 · Answer 2 · edited May 23 '17 at 10:32

If I've not missed something, your query could be made much simplier:

select
    sum(case when <condition here> then 1 else 0 end) as cnt1
    count(*) as cnt2
from EMCDX5y_test

If you're really want to keep your query as is, you can use

select
    (first query) as <something>,
    (second query) as <something>

or, in your terms:

select
   (
       select count(t.X2)
       from EMCDX5y_test as t
       where
          convert(decimal(8,3), t.X2) < 
          (
               select tt.X2
               from EMCDX5y_test as tt
               where tt.X1 = (select max(ttt.X1) from EMCDX5y_test as ttt)
          )
   ) as rank,
   (
       select count(t.X2)
       from EMCDX5y_test as t
   ) as total

note aliases and <alias>.<column notation> - it's not really safe to use massive subqueries without aliases - see SQL IN query produces strange result

trouble in SQL join two SELECT statement

2 Answers2