Filter and Count Distinct Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filter and Count Distinct Data

Author

Topic

Code128
Starting Member

3 Posts

Posted - 2011-10-13 : 04:00:38

I’m trying to use a SQL string to filter some data. The original data is like this:

Part_Number,Description,Crate_ID
68015000221,MTM800E QUICK START GUIDE - AR,A100
6804113J25,SAFETY LEAFLET-TETRA MOBILE,A100
6864117B25,SAFETY LEAFLET - EMEA,A100
6866534D68,R&TTE LEAFTLET MTM700,A100
GKN6274A,PWR CBL 3086026B02 10A FUSE,A100
GLN7324A,ARI LO PROFILE TRUNNION KIT,A100
GMBN1021A,ACCESSORY CONNECTION KIT,A100
GMSN4078B,SMALL SPEAKER 5W,A100
GMWN4301B,NG CONTROL HEAD ARABIC,A100
PMUE4465C,MTM800 ENH 380-430 TEA1,A100
RMN5107B,COMPACT MOBILE MIC,A100
68015000221,MTM800E QUICK START GUIDE - AR,A100
6804113J25,SAFETY LEAFLET-TETRA MOBILE,A100
6864117B25,SAFETY LEAFLET - EMEA,A100
6866534D68,R&TTE LEAFTLET MTM700,A100
GKN6274A,PWR CBL 3086026B02 10A FUSE,A100
GLN7324A,ARI LO PROFILE TRUNNION KIT,A100
GMBN1021A,ACCESSORY CONNECTION KIT,A100
GMSN4078B,SMALL SPEAKER 5W,A100
GMWN4301B,NG CONTROL HEAD ARABIC,A100
PMUE4465C,MTM800 ENH 380-430 TEA1,A100
RMN5107B,COMPACT MOBILE MIC,A100
68015000221,MTM800E QUICK START GUIDE - AR,A101
6804113J25,SAFETY LEAFLET-TETRA MOBILE,A101
6864117B25,SAFETY LEAFLET - EMEA,A101
6866534D68,R&TTE LEAFTLET MTM700,A101
GKN6266A,PWR CBL DC BASE RENA,A101
GLN7318A,BASE TRAY W/O SPKR ARIANE,A101
GMBN1021A,ACCESSORY CONNECTION KIT,A101
GMSN4078B,SMALL SPEAKER 5W,A101
GMWN4301B,NG CONTROL HEAD ARABIC,A101
GPN6145B,MOBILE RADIO PSU 138W,A101
NTN7374AR,220V CHGR LINE CORD,A101
PMUE4465C,MTM800 ENH 380-430 TEA1,A101
RMN5106A,DESKTOP MIC,A101

I would like to filter the distinct records based on Part Number, Description and crate ID and also count the number of duplicate records to give me the quantity of part numbers that are the same. This would give a summary table looking like below. This now tells me how many part number items are in each crate ID.

Part_Number,Description,Crate_ID,qty
68015000221,MTM800E QUICK START GUIDE - AR,A100,2
68015000221,MTM800E QUICK START GUIDE - AR,A101,1
6804113J25,SAFETY LEAFLET-TETRA MOBILE,A100,2
6804113J25,SAFETY LEAFLET-TETRA MOBILE,A101,1
6864117B25,SAFETY LEAFLET - EMEA,A100,2
6864117B25,SAFETY LEAFLET - EMEA,A101,1
6866534D68,R&TTE LEAFTLET MTM700,A100,2
6866534D68,R&TTE LEAFTLET MTM700,A101,1
etc……

The SQL query
“SELECT DISTINCT part_number, description, crate_id from inventory”
gets me close and

“SELECT part_number, COUNT(part_number) as qty from inventory group by part_number”
gives me the quantity figure I need. I become stuck trying to join these together to make one query and one table of results.

Any thoughts?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-13 : 04:18:11

SELECT part_number, description, crate_id,COUNT(*) as qty from inventory
group by part_number, description, crate_id

No, you're never too old to Yak'n'Roll if you're too young to die.