Count number of records in table in SQL Server

smile
 
on Sep 20, 2021 01:15 AM
416 Views

I wrote the following query

SELECT t.TestID,t.TagID,t.C_Date
FROM
    (
      SELECT ROW_NUMBER() OVER (PARTITION BY (TagID) ORDER BY C_Date DESC) Row_No,TestID,TagID,C_Date
      FROM tblPregTest where P_Result='Positive'
      GROUP BY TestID,TagID,C_Date
    )t
WHERE Row_No = 1 AND t.TagID NOT IN (SELECT DISTINCT TagID FROM tblCalving)
ORDER BY t.TestID

it is showing me correct data like that

TestID

TagID

Date

1

101

2021-02-01

2

102

2021-02-01

3

103

2021-02-01

4

104

2021-02-01

5

105

2021-02-01

6

106

2021-02-01

Now, I want to display data like that

Head

Count

Pregnant

6

I am unable to get required out. how to get solution pls.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 20, 2021 05:03 AM

Use below query.

SQL

SELECT 'Pregnant' AS 'Head',COUNT(TestID) 'Count'
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY (TagID) ORDER BY C_Date DESC) Row_No,TestID,TagID,C_Date
    FROM tblPregTest where P_Result='Positive'
    GROUP BY TestID,TagID,C_Date
)t
WHERE Row_No = 1 AND t.TagID NOT IN (SELECT DISTINCT TagID FROM tblCalving)
ORDER BY t.TestID