trying to group data - SQL Server Forums

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

trying to group data

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2012-01-20 : 20:02:21

so question, i'm trying to group some data, and also get a # of occurrences when i do it. here's a sample table:

select 'ALBERT'fn,'KOHL'ln,'123 MAIN ST'addr,'PHOENIX'city,'AZ'st,'55555'zip,'1233'id,'2011-01-01'date into #temp union all
select 'ALBERT','KOHL','123 MAIN ST','PHOENIX','AZ','55555','1234','2011-01-22' union all
select 'ALBERT','KOHL','123 MAIN ST','PHOENIX','AZ','55555','1235','2011-03-18'

now basically, i'm looking to get:


fn     ln   addr        city    st   zip   id   date       hits
------ ---- ----------- ------- ---- ----- ---- ---------- ----
ALBERT KOHL 123 MAIN ST PHOENIX AZ   55555 1235 2011-03-18 3

now i'm doing this w/ a database of about 800 million rows, i would probably write it loop though the database by SCF Codes so i can be a few million records @ a time.

i would like to get the data unique by fn,ln,addr,zip

any help would be greatly appreciated

singularity
Posting Yak Master

153 Posts

Posted - 2012-01-20 : 21:11:36

[code]
select fn, ln, addr, city, zip, max(id) as id, max([date]) as [date], count(*) as hits
from yourtable
group by fn, ln, addr, city, zip
[/code]