Add value to a count from another count

Transact-SQL
1

Is it possible to add a value to a count from another count, if so how?

I want to add the value from count(case when NF = '1' then 1 else null end) as NF to count(id) AS [entries]

        Dim MyDataAdapter As New SqlDataAdapter("SELECT Klass, " &
        "count(case when Grupp = 'A' then 1 else null end) as A, " &
        "count(case when Grupp = 'B' then 1 else null end) as B, " &
        "count(case when Grupp = 'C' then 1 else null end) as C, " &
        "count(DISTINCT Tävlingens_namn) AS [races], " &
        "count(id) AS [entries], " &
        "count(case when NF = '1' then 1 else null end) as NF, " &
        "count(Sluttid) AS [finished], " &
        "count(DISTINCT Arrangör) AS [arr], " &
        "count(FIDnr) AS [totfid] " &
        "FROM resultat " &
        "Where Stil = @Stil " &
        "AND Datum BETWEEN @startdate AND @enddate " &
        "GROUP BY Klass Order By Klass", MyConnection)
2 
count(case when NF = '1' then 1 else null end) + count(id) AS [entries]
3

Thanks :slight_smile:

4

You’re welcome!

5

image
image626×303 83.9 KB

6 
-- Direct
COUNT(id) + COUNT(CASE WHEN NF = '1' THEN 1 END) AS entries_plus_nf

-- CTE
WITH agg AS (
  SELECT Klass,
         COUNT(id) AS entries,
         COUNT(CASE WHEN NF = '1' THEN 1 END) AS NF
  FROM resultat
  GROUP BY Klass
)
SELECT Klass, entries, NF, entries + NF AS entries_plus_nf
FROM agg;

-- Subquery
SELECT Klass, entries, NF, entries + NF AS entries_plus_nf
FROM (
  SELECT Klass,
         COUNT(id) AS entries,
         COUNT(CASE WHEN NF = '1' THEN 1 END) AS NF
  FROM resultat
  GROUP BY Klass
) q;

-- SUM
SELECT Klass,
       SUM(1) AS entries,
       SUM(CASE WHEN NF = '1' THEN 1 ELSE 0 END) AS NF,
       SUM(1) + SUM(CASE WHEN NF = '1' THEN 1 ELSE 0 END) AS entries_plus_nf
FROM resultat
GROUP BY Klass;

-- View
CREATE VIEW v_resultat_counts AS
SELECT Klass,
       COUNT(id) AS entries,
       COUNT(CASE WHEN NF = '1' THEN 1 END) AS NF,
       COUNT(id) + COUNT(CASE WHEN NF = '1' THEN 1 END) AS entries_plus_nf
FROM resultat
GROUP BY Klass;
7

:winking_face_with_tongue:

hope this helps

8

Thanks for the info, very interesting :slight_smile:

But i get: Incorrect syntax: 'CREATE VIEW' must be the only statement in a query batch.?

9 
Put CREATE VIEW in its own batch. In SQL Server Management Studio (SSMS), 
you can separate batches with the GO keyword

CREATE VIEW MyView AS
SELECT column1, column2
FROM MyTable;
GO