SQL - Assigning a query result to variable and then applying in a column

Dear Experts,

Being not sure whether this question is easy or hard to complete, basically having a simple query

SELECT
	Count(DISTINCT AP.LFB1.BUKRS) AS TotalCCs
FROM
	AP.LFB1

Open in new window


which results a single value 17 (so many Company codes exist in SAP)

I would like to use this value in the later queries, could you please advise about the best method regarding that?

Should be maybe assigned to a variable somehow and applying in the formulas?

Thanks in advance,
LVL 1
csehzIT consultantAsked:
Who is Participating?
 
Lee SavidgeCommented:
declare @iTotalCCs int

SELECT
	@iTotalCCs = Count(DISTINCT AP.LFB1.BUKRS)
FROM
	AP.LFB1

select iTotalCCs

Open in new window

0
 
Lee SavidgeCommented:
This?

declare @iTotalCCs int

SELECT
	@iTotalCCs = Count(DISTINCT AP.LFB1.BUKRS) AS TotalCCs
FROM
	AP.LFB1

select iTotalCCs

Open in new window

0
 
csehzIT consultantAuthor Commented:
Thanks for the answer, basically running that code it brings an error message


Incorrect syntax near the keyword 'AS'.


Could you please advise where that is incorrect in the syntax?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Dorababu MSenior Software EngineerCommented:
Are you looking something similar to this ?

DECLARE @Testtbl Table
(
	Id int,
	SName nvarchar(100)
)

INSERT INTO @Testtbl values(1,'Test'),(1,'Test'),(1,'Test'),
(1,'Test')

declare @iTotalCCs int

SELECT @iTotalCCs = x.count
FROM
( SELECT
	  COUNT ( DISTINCT(Id) )  count
FROM
	@Testtbl) as x

	SELECT @iTotalCCs

Open in new window

0
 
csehzIT consultantAuthor Commented:
That is brilliant, thank you for both of the solutions
0
 
Mark WillsTopic AdvisorCommented:
I know it has already been answered.... But...

You can also use CTE which is a common table expression. It really depends on how you plan to use that information.

Basically you Name a query, then you can use those results to do more. Bit like a subquery, except you do it first as a query.

;WITH TotalCC as
( SELECT Count(DISTINCT AP.LFB1.BUKRS) AS CountCCs
  FROM   AP.LFB1
) SELECT CountCCs from TotalCC    -- here I am selecting from the CTE named TotalCC, or could use the CTE like any other table

Open in new window


Those CTE's can be nested. But if it isnt a related / nested query, then declaring a variable means the variable can exists from query to query within a batch...
1
 
csehzIT consultantAuthor Commented:
Mark thank you for mentioning that option, basically I like in the variable kind of version that later it can be used in queries without the need of joins
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.