Sql Server odpowiednik funkcji agregującej COUNTIF

Buduję zapytanie z GROUP BYklauzulą, która wymaga możliwości liczenia rekordów tylko na podstawie określonego warunku (np. Licz tylko rekordy, w których określona wartość kolumny jest równa 1).

SELECT  UID, 
        COUNT(UID) AS TotalRecords, 
        SUM(ContractDollars) AS ContractDollars,
        (COUNTIF(MyColumn, 1) / COUNT(UID) * 100) -- Get the average of all records that are 1
FROM    dbo.AD_CurrentView
GROUP BY UID
HAVING  SUM(ContractDollars) >= 500000

COUNTIF()Linia oczywiście nie powiedzie się, ponieważ nie ma natywna funkcja SQL nazywa COUNTIF, ale idea jest tu określenie procentu wszystkich wierszy, które mają wartość „1” dla MyColumn.

Czy są jakieś przemyślenia, jak poprawnie zaimplementować to w środowisku MS SQL 2005?

Możesz użyć SUM(nie COUNT!) W połączeniu z CASEinstrukcją, na przykład:

SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

Uwaga: w moich własnych testach NULLnie było problemu, chociaż może to zależeć od środowiska. Możesz obsłużyć wartości zerowe, takie jak:

SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

Zwykle robię to, co zalecił Josh, ale przeprowadziłem burzę mózgów i przetestowałem nieco hokerską alternatywę, którą chciałem się podzielić.

Możesz wykorzystać fakt, że COUNT (ColumnName) nie liczy wartości NULL i użyć czegoś takiego:

SELECT COUNT(NULLIF(0, myColumn))
FROM AD_CurrentView

NULLIF - zwraca NULL, jeśli dwie przekazane wartości są takie same.

Zaleta: Wyraża zamiar COUNT wierszy zamiast notacji SUMA (). Wada: nie jest tak jasne, jak to działa („magia” jest zwykle zła).

Użyłbym tej składni. Osiąga to samo, co sugestie Josha i Chrisa, ale ma tę zaletę, że jest zgodny z ANSI i nie jest powiązany z konkretnym dostawcą bazy danych.

select count(case when myColumn = 1 then 1 else null end)
from   AD_CurrentView

Dodając do odpowiedzi Josha,

SELECT COUNT(CASE WHEN myColumn=1 THEN AD_CurrentView.PrimaryKeyColumn ELSE NULL END)
FROM AD_CurrentView

U mnie zadziałało (w SQL Server 2012) bez zmiany „licznika” na „sumę”, a ta sama logika jest przenoszona na inne „agregacje warunkowe”. Np. Sumowanie na podstawie warunku:

SELECT SUM(CASE WHEN myColumn=1 THEN AD_CurrentView.NumberColumn ELSE 0 END)
FROM AD_CurrentView

Co powiesz na

SELECT id, COUNT(IF status=42 THEN 1 ENDIF) AS cnt
FROM table
GROUP BY table

Krótszy niż CASE:)

Działa, ponieważ COUNT()nie liczy wartości null i IF/ CASEzwraca wartość null, gdy warunek nie jest spełniony i nie ma ELSE.

Myślę, że to lepsze niż używanie SUM().

Nie jest to specyficzne dla produktu, ale standard SQL zapewnia

SELECT COUNT() FILTER WHERE <condition-1>, COUNT() FILTER WHERE <condition-2>, ... FROM ...

w tym celu. Lub coś, co bardzo to przypomina, nie wiem z góry mojego kapelusza.

I oczywiście dostawcy będą woleli trzymać się swoich zastrzeżonych rozwiązań.

Dlaczego nie tak?

SELECT count(1)
FROM AD_CurrentView
WHERE myColumn=1

Musiałem użyć LICZ.JEŻELI () w moim przypadku jako część moich kolumn WYBIERZ ORAZ, aby naśladować% liczby przypadków, w których każdy element pojawił się w moich wynikach.

Więc użyłem tego ...

SELECT COL1, COL2, ... ETC
       (1 / SELECT a.vcount 
            FROM (SELECT vm2.visit_id, count(*) AS vcount 
                  FROM dbo.visitmanifests AS vm2 
                  WHERE vm2.inactive = 0 AND vm2.visit_id = vm.Visit_ID 
                  GROUP BY vm2.visit_id) AS a)) AS [No of Visits],
       COL xyz
FROM etc etc

Oczywiście będziesz musiał sformatować wynik zgodnie z wymaganiami dotyczącymi wyświetlania.

SELECT COALESCE(IF(myColumn = 1,COUNT(DISTINCT NumberColumn),NULL),0) column1,
COALESCE(CASE WHEN myColumn = 1 THEN COUNT(DISTINCT NumberColumn) ELSE NULL END,0) AS column2
FROM AD_CurrentView