Trouble with Count, etc.

[seraphis]

I have made a table with more than 400000 rows. I first collected columns from the base table and then used left joins with 4 other tables to create my final table. 

One column in the final table is named 'AssetSerialNumber". I want to count it grouping it by itself because each  'AssetSerialNumber' is present in more than one row (sometimes more than a thousand). In other words, I want a count of every asset serial number. 

To make the code work faster, I created a new table from the final table with only 'AssetSerialNumber' as a column. When I do this I get only unique asset serial numbers (say about 200000) and not the 400000 rows. I want to know why this is happening. When I create a new table with only one column from another table, I expect all the rows but this is not happening.  However, when I pull in another row (primary key the case number) then I think I am getting the right number of rows.

The second issue is that even when I get the right number of rows I am not able to get the correct count of the asset serial numbers - I have written a query with 'AssetSerialNumber' and count('AssetSerialNumber') group by 'AssetSerialNumber' - the query is running and I am not getting any error. Since an asset is present in multiple rows, then the same asset number (say ASD359) will show the same count in the query results-this is understood. Nevertheless, what is happening is that that many different assets are showing exactly the same count and this is wrong.

Can anyone help me and let me know why the above is happening and how I can get the correct results?

Here is the code

Case1:

////several lines of code for Case1. This is made by several left joins.

NoConcatenate

Case2:
load Case_Flat_Case.Asset_Serial_Number
Resident Case1;

NoConcatenate

Case3:
load Case_Flat_Case.Asset_Serial_Number, Count(Case_Flat_Case.Asset_Serial_Number)
Resident Case2 group by Case_Flat_Case.Asset_Serial_Number;

Thanks

Manish

[theglenndavid]

Perhaps you can share the relevant code snippets so as to help us understand the issue you are describing.

[seraphis]

Hi I have added the code in my initial post.

[tresesco]

The code should work fine. Have you tested with smaller sample size data? It could be something to do with your data and understanding around that, or may be other section of your code impacting the result.

[seraphis]

I have worked out a solution by placing the count code towards the top of the script (before the several Left Joins and just after the first table). The problem was happening because of the several left joins and it was too time-consuming to diagnose all that. 

Thanks for all your help and support.

[tresesco]

Try like:

Count(Case_Flat_Case.Asset_Serial_Number) as "AssetCount",
if(Count(Case_Flat_Case.Asset_Serial_Number) > 1,'Yes', 'No') as Flag
Resident Case1 group by Case_Flat_Case.Asset_Serial_Number; 

[seraphis]

Yeah, I tried that later and it worked-that is why I removed the code. Thanks a lot for your support.