CODEWITHSUNDEEP
sqlsql-servercountif
BobSki
BobSki

Reputation: 1552

counting records in a query

I'm querying data from SQL SERVER, and using a lot of CASE statement to get the appropriate results. After the data is queries, I dump it to excel. Here's what I'm trying to achieve...

Query looks something like...

Select ClientName, ClientNo, 
   Case when AAA=0 then 'N' Else 'Y' End As AAA,
   Case when BBB=0 then 'N' Else 'Y' End as BBB,
   Case when CCC=0 then 'N' Else 'Y' End as CCC
From tblClientInformation

So this is a much shortened version of the query, I actually have about 17 Case statements. Once I dump this data to Excel spreadsheet, I do a COUNTIF function to take a count of all Y values for each clientName/ClientNO, so the data looks something like...

ClientName    ClientNo     AAA        BBB       CCC       TotalY
Bob Smith     1775         Y          N         Y         2

So in TotalY I basically use CountIF function in excel to count all the Y values starting in AAA - ZZZ (example). However, since my query yields around 12000 records, it seems to be quite time consuming.

Is there any way that I can count the Y's in the query so that by the time my data is queried in SQL SERVER, all I have to do is simply dump it in excel, rather than adding on the Column TotalY with CountIF function?

Upvotes: 1

Views: 63

Answers (2)

Eli
Eli

Reputation: 2608

What are the data types for fields AAA, BBB, and CCC? If they're integers, what are the possible values? If it is of some numeric data type, and the only values are 0 and 1, you can do it without a CTE

Select 
    ClientName,
    ClientNo, 
    Case when AAA=0 then 'N' Else 'Y' End As AAA,
    Case when BBB=0 then 'N' Else 'Y' End as BBB,
    Case when CCC=0 then 'N' Else 'Y' End as CCC,
    AAA+BBB+CCC as TotalY
From tblClientInformation

If those three fields can have different values, we would be able to adjust to that as well.

Update: As per a comment from the OP, there can be values other than 0 or 1 for fields AAA, BBB, and CCC; that being the case, I have modified the query to have a nested query to achieve the same results without resorting to a CTE. Since we aren't doing anything recursive, the CTE will have no performance benefit here (I tested with 2.5M rows) and many people find a CTE to be harder to read... Here's the updated query: 

SELECT 
    ClientName
    ,ClientNo
    ,AAA
    ,BBB
    ,CCC
    ,LEN(REPLACE(AAA+BBB+CCC, 'N',''))
FROM (
    SELECT 
        ClientName,
        ClientNo, 
        CASE WHEN AAA=0 THEN 'N' ELSE 'Y' END AS AAA,
        CASE WHEN BBB=0 THEN 'N' ELSE 'Y' END AS BBB,
        CASE WHEN CCC=0 THEN 'N' ELSE 'Y' END AS CCC
    FROM tblClientInformation
)a

Upvotes: 1

dani herrera
dani herrera

Reputation: 51665

You can compose a new query over your own with CTE:

;WITH table_without_count as
( Select ClientName, ClientNo, 
   Case when AAA=0 then 'N' Else 'Y' End As AAA,
   Case when BBB=0 then 'N' Else 'Y' End as BBB,
   Case when CCC=0 then 'N' Else 'Y' End as CCC
  From tblClientInformation
)
select *, LEN(REPLACE(AAA+BBB+..., 'N', '')) as TotalY
from table_without_count

Upvotes: 2

Related Questions