Greetings,

I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records. Any insight would be greatly appreciated!

CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
	[Installation] [numeric](38, 8) NULL,
	[AccountType] [nvarchar](50) NULL,
	[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



SELECT 
    PWSID
    , COUNT(Instalacion) AS Numeroclientes  
    , SUM(CASE WHEN ClaseCuenta = 'AAA' THEN 1 ELSE 0 END) AAA
    , SUM(CASE WHEN ClaseCuenta = 'Comerical' THEN 1 ELSE 0 END) Comercial
    , SUM(CASE WHEN ClaseCuenta = 'Entidad Sin Fines De Lucro' THEN 1 ELSE 0 END) SinFinesDeLucro
    , SUM(CASE WHEN ClaseCuenta = 'Gobierno' THEN 1 ELSE 0 END) Gobierno
    , SUM(CASE WHEN ClaseCuenta = 'Gobierno Fondo General' THEN 1 ELSE 0 END) FondoGeneral
    , SUM(CASE WHEN ClaseCuenta = 'Industrial' THEN 1 ELSE 0 END) Industrial
    , SUM(CASE WHEN ClaseCuenta = 'Residencial' THEN 1 ELSE 0 END) Residencial
    , SUM(CASE WHEN ClaseCuenta = 'Residencial Pblico' THEN 1 ELSE 0 END) ResidencialPblico
    , SUM(CASE WHEN ClaseCuenta IS NULL THEN 1 ELSE 0 END) NULOS
        --,Municipio, COUNT(Instalacion) AS NUmeroClientes 
FROM 
    GeoReferenciaCatalogo.dbo.CATASTRO_PSWID_SHPMUNINEW (NOLOCK)
GROUP BY 
    PWSID
    --, Clasecuenta--, Municipio
ORDER BY 
    PWSID
    --, ClaseCuenta--, Municipio

 

• Edited by RicanGirl1989 12 hours 8 minutes ago
• Changed type RicanGirl1989 11 hours 35 minutes ago Question was reclassified as I found the issue
• Changed type Katherine XiongMicrosoft contingent staff, Moderator 4 hours 45 minutes ago It's appropriate to post a reply to share your solution and then mark it as answer rather than changing the question type to discussion.

You have to be missing one of the cases for your AccountType...

Try this first:

SELECT  ClaseCuenta
    
FROM 
    GeoReferenciaCatalogo.dbo.CATASTRO_PSWID_SHPMUNINEW (NOLOCK)
GROUP BY 
    ClaseCuenta

This will give you all distinct cases for your AccountType, compare it to your sums to figure out which one is missing.

HTH

First of all, get rid of the (NOLOCK) keyword.

Secondly, just do

select distinct ClaseCuenta from myTable

Most likely there is another category you're not considering.

I wasn't missing an accounttype but had actually misspelled one! My mistake for not paying attention to detail.

Thanks for the help!

I actually used (NOLOCK) because the table is huge and is constantly getting updated by other transactions. I didn't want to issue a lock against the table that would impede other transactions from modifying the data. 

• Edited by RicanGirl1989 11 hours 51 minutes ago

I actually see now that you misspelled 'Comercial'. All 0 in that category was another clue.

Thanks for the help!

Hi RicanGirl1989,

Glad to heard that the issue had been solved. It's appropriate to post a reply to share your solution and then mark it as answer rather than changing the question type to discussion. That way, it will be very beneficial for other community members who have similar questions.

Thanks,
Katherine Xiong

I actually used (NOLOCK) because the table is huge and is constantly getting updated by other transactions. I didn't want to issue a lock against the table that would impede other transactions from modifying the data. 

Select statements don't issue locks. Unless you are only interested in approximations, the use of NOLOCKs can provide dramatically inaccurate results.