CODEWITHSUNDEEP
sql-server
K.Luth
K.Luth

Reputation: 135

The multi-part identifier could not be bound WITH clause

I have calculated the TOP 10 running total of a table and now want to get this in percentage for a pareto diagram, I have the following code:

WITH
CTE as
(
    SELECT 
       [nWordNr] as W, 
       [nBitNr] as B,
       SUM([tmTotals]) as total,
       COUNT(*) as Amount
    FROM Messages_History
    GROUP BY nWordNr, nBitNr
),
totAlarms as
(
    select 
    count([nWordNr]) as cnt
    FROM Messages_History
)
SELECT TOP 10 *, 
    SUM(total) OVER (ORDER BY total desc) * 1.0 / totAlarms.cnt as Running
FROM   CTE
ORDER BY total desc

When I want to use totAlarms.cnt to calculate the running percentage I get the following error:

The multi-part identifier "totAlarms.cnt" could not be bound.

How can I use totAlarms.cnt to calculate the running percentage.

Upvotes: 2

Views: 954

Answers (4)

IMSoP
IMSoP

Reputation: 97948

CTEs aren't automatically included in the attached query, you have to mention them in a FROM or JOIN clause.

Your current query is of the form:

WITH
   CTE AS ( ... ),
   totAlarms  AS ( ... )
SELECT ...
FROM CTE
ORDER BY ...

So only CTE is visible in the SELECT clause. totAlarms is defined but never used - like creating a VIEW but not writing any queries that reference it.

Since your two CTEs are independent, you need to CROSS JOIN them, giving this form:

WITH
   CTE AS ( ... ),
   totAlarms  AS ( ... )
SELECT ...
FROM CTE
CROSS JOIN totAlarms
ORDER BY ...

Or using the , operator, which is effectively a cross join:

WITH
   CTE AS ( ... ),
   totAlarms  AS ( ... )
SELECT ...
FROM CTE, totAlarms
ORDER BY ...

Upvotes: 2

Nitika
Nitika

Reputation: 463

WITH
CTE as
(
SELECT 
     [nWordNr] as W, 
     [nBitNr] as B,
     SUM([tmTotals]) as total,
     COUNT(*) as Amount
FROM Messages_History
GROUP BY nWordNr, nBitNr
),
  totAlarms as
  (
  select 
  count([nWordNr]) as cnt
  FROM Messages_History
  )
SELECT TOP 10 *, 
   SUM(total) OVER (ORDER BY total desc) * 1.0 / totAlarms.cnt as Running
FROM   CTE,totAlarms 
ORDER BY total desc

With the help of cross join you can achieve it. The multi-part identifier "totAlarms.cnt" could not be bound. This error was showing becoz you are not using totAlarms (CTE table) in join or from.

Upvotes: 3

Chanukya
Chanukya

Reputation: 5893

Instead of writing sub CTE's try to create variable and store value and use like below

declare  @nWordNr int
select @nWordNr=count(nWordNr)  FROM Messages_History

;WITH
CTE as
(
SELECT 
[nWordNr] as W, 
[nBitNr] as B,
SUM([tmTotals]) as total,
COUNT(*) as Amount
FROM Messages_History
GROUP BY nWordNr, nBitNr
)--,
--totAlarms as
--(
--select 
--count([nWordNr]) as cnt
--FROM Messages_History
--)
SELECT TOP 10 *, 
SUM(total) OVER (ORDER BY total desc) * 1.0 / @nWordNr as Running
FROM   CTE
ORDER BY total desc

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15150

Since the count is just a single value, I'd capture it in a scalar variable:

DECLARE @totAlarms_cnt INT = (
                                  SELECT    COUNT([nWordNr]) as cnt
                                  FROM      Messages_History
                                  )
;
WITH
CTE as
(
SELECT 
     [nWordNr] as W, 
     [nBitNr] as B,
     SUM([tmTotals]) as total,
     COUNT(*) as Amount
FROM Messages_History
GROUP BY nWordNr, nBitNr
)

SELECT TOP 10 *, 
   SUM(total) OVER (ORDER BY total desc) * 1.0 / @totAlarms_cnt as Running
FROM   CTE
ORDER BY total desc

Upvotes: 1

Related Questions