SQL Server Two select statements, two where clauses, one number

s4n0splo 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(85)

I am trying to create a query with two diferent SELECT statements and two diferent WHERE clauses. But the WHERE clauses are the same number. They cannot be joined because one is in varchar and the other is int . But I want to input the number only once.

Maybe this will make it easier to understand. I have this:

SELECT
    (
        SELECT
            COUNT(Apples)
        FROM
            applestracker
        WHERE
            ( seller LIKE '3')
    )
    +
    (
        SELECT
            COUNT(Oranges)
        FROM
            orangestracker
        WHERE
            ( sellernumber = 3 )
    ) AS Total

I want it so that I don't have to put the number 3 twice.

Something like this :

SELECT
    (
        SELECT
            COUNT(Apples)
        FROM
            applestracker
        WHERE
            ( seller LIKE 'Number_of_seller' )
    )
    +
    (
        SELECT
            COUNT(Oranges)
        FROM
            orangestracker
        WHERE
            ( sellernumber = Number_of_seller )

    -- Number_of_seller is 3
    ) AS Total

Is something like this possible. I don't want to have to repeat the number of seller twice even though they are two types. Is it possible to create a where clause standby and then put the number in the end? Thank you

Join is not possible in this case. I have try to create a table but I am not being able to create a single table with the sum of two different data bases

sql-server

来源：https://stackoverflow.com/questions/76662658/two-select-statements-two-where-clauses-one-number

2条答案

按热度按时间

izj3ouym1#

You can declare variable up front and use it in different parts of your query, convert it to different types as needed.

DECLARE @var INT;
SET @var = 3;

Select 
(
  select count(Apples)
  from applestracker
  where 
  seller like CAST(@var AS VARCHAR(11))
) 
+
(
  select Count (Oranges)
  from orangestracker
  where
  sellernumber = @var
)  
AS Total

赞(0）回复(0）举报 2023-08-02

a0zr77ik2#

At risk of being verbose, you can DECLARE that as a variable: use DECLARE @number_of_seller int = 3 ,

SQL Server will implicitly attempt to convert varchar values to int in your WHERE clauses, though your query won't be SARGable . Or if you use a separate @variable for the varchar version it should use indexes on that column - though you won't need to specify 3 more than once:

Like so:

DECLARE @number_of_seller       int         = 3;
DECLARE @number_of_seller_chars varchar(10) = CONVERT( varchar(10), @number_of_seller );

SELECT
    (
        SELECT
            COUNT( a.Apples )
        FROM
            applestracker AS a
        WHERE
            a.seller = @number_of_seller_chars
    )
    +
    (
        SELECT
            COUNT( o.Oranges )
        FROM
            orangestracker AS o
        WHERE
            o.sellernumber = @number_of_seller
    ) AS Total

...that said, SQL Server is smart enough to recognize constant-expressions in queries, so the above query can be rewritten as below without any performance impact:

DECLARE @number_of_seller int = 3;

SELECT
    (
        SELECT
            COUNT( a.Apples )
        FROM
            applestracker AS a
        WHERE
            a.seller = CONVERT( varchar(10), @number_of_seller )
    )
    +
    (
        SELECT
            COUNT( o.Oranges )
        FROM
            orangestracker AS o
        WHERE
            o.sellernumber = @number_of_seller
    ) AS Total

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Two select statements, two where clauses, one number

2条答案

相关问题

热门标签

最新问答