SQL Select Query-grouping and merging of 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

SQL Select Query-grouping and merging of data

Author

Topic

barnabyb
Starting Member

2 Posts

Posted - 2008-07-10 : 18:06:46

Hi!

I'm relatively new to SQL Select Queries, and have managed to do a Cross Tab query in SQL Server to get the results that look somthing like this:

JobId, Date, HoleId, Location, SubLocation, SubSubLocation, ChemResulta, ChemicalResultb etc....

You can see that there are 3 tiers of Locations. As there are many samples from the same subsublocation, I need to do the following:

1. For each group of records for each subsublocation, only pick one record - the one with the latest date.
2. Group the SubSubLocations together and find the average for each chemical reading.

If anyone has any suggestions, please let me know. I'm doing this as a Select Query inside an ASP2.NET web application, but nearly had a seizure when creating the cross-tab query - see below. If anyone can help me, please hand me some suggestions, or even pointers!

Cheers,

Barney

SQL Query so far (works!!!)

SELECT HoleId, SampleType, AreaType, Location, SubLocat, SubLocat2, DateSur, Eastings, Northings, Depth_mbgl, Depth_AOD, AsbestosPresent, AsbestosType, MIN(CASE WHEN ChemID = '10401' THEN SampleReading END) AS 'pH', MIN(CASE WHEN ChemID = '10403' THEN SampleReading END) AS 'MC', MIN(CASE WHEN ChemId = '10007' THEN SampleReading END) AS 'Acenaphthene', MIN(CASE WHEN ChemID = '10008' THEN SampleReading END) AS 'Acenaphthylene', MIN(CASE WHEN ChemID = '10009' THEN SampleReading END) AS 'Anthracene', MIN(CASE WHEN ChemID = '10010' THEN SampleReading END) AS 'Benzo(a)anthracene', MIN(CASE WHEN ChemID = '10011' THEN SampleReading END) AS 'Benzo(a)pyrene', MIN(CASE WHEN ChemID = '10012' THEN SampleReading END) AS 'Benzo(b)fluoranthene',
MIN(CASE WHEN ChemID = '10013' THEN SampleReading END) AS 'Benzo(g,h,i)perylene', MIN(CASE WHEN ChemID = '10014' THEN SampleReading END) AS 'Benzo(k)fluoranthene', MIN(CASE WHEN ChemID = '10015' THEN SampleReading END) AS 'Chrysene', MIN(CASE WHEN ChemID = '10016' THEN SampleReading END) AS 'Dibenzo(a,h)anthracene', MIN(CASE WHEN ChemID = '10017' THEN SampleReading END) AS 'Fluoranthene', MIN(CASE WHEN ChemID = '10018' THEN SampleReading END) AS 'Fluorene', MIN(CASE WHEN ChemID = '10019' THEN SampleReading END) AS 'Indeno(1,2,3-cd)pyrene', MIN(CASE WHEN ChemID = '10020' THEN SampleReading END) AS 'Naphthalene', MIN(CASE WHEN ChemID = '10021' THEN SampleReading END) AS 'Phenanthrene', MIN(CASE WHEN ChemID = '10022' THEN SampleReading END) AS 'Pyrene', MIN(CASE WHEN ChemID = '10023' THEN SampleReading END) AS 'Total 16 PAHs', MIN(CASE WHEN ChemID = '10024' THEN SampleReading END) AS 'Total Carcinogenic 6 PAHs', MIN(CASE WHEN ChemID = '10342' THEN SampleReading END) AS 'As', MIN(CASE WHEN ChemID = '10351' THEN SampleReading END) AS 'Cd', MIN(CASE WHEN ChemID = '10358' THEN SampleReading END) AS 'Cr', MIN(CASE WHEN ChemID = '10360' THEN SampleReading END) AS 'Cu', MIN(CASE WHEN ChemID = '10363' THEN SampleReading END) AS 'Fe', MIN(CASE WHEN ChemID = '10366' THEN SampleReading END) AS 'Hg', MIN(CASE WHEN ChemID = '10372' THEN SampleReading END) AS 'Ni', MIN(CASE WHEN ChemID = '10380' THEN SampleReading END) AS 'Pb', MIN(CASE WHEN ChemID = '10384' THEN SampleReading END) AS 'S', MIN(CASE WHEN ChemID = '10390' THEN SampleReading END) AS 'Se', MIN(CASE WHEN ChemID = '10399' THEN SampleReading END) AS 'Zn'

FROM (SELECT HoleId, ChemID, SampleReading, DateSur, Northings, Eastings, Depth_mbgl, Depth_AOD, AreaType, Location, SubLocat, SubLocat2, SampleType, AsbestosPresent, AsbestosType
FROM Tbl_Samples) AS derivedtbl_1

GROUP BY HoleId, SampleType, AreaType, Location, SubLocat, SubLocat2, DateSur, Eastings, Northings, Depth_mbgl, Depth_AOD, AsbestosPresent, AsbestosType

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-11 : 01:10:00

Your posted query doesnt seem to find out average. you are taking only MIN. can you explain why?

barnabyb
Starting Member

2 Posts

Posted - 2008-07-11 : 03:18:12

Simply, there is just one chemical reading of a certain type per HoleId.

The data is imported from an Excel spreadsheet, and the Primary Key rules means that the same sampleId cannot be imported twice, so I guess that MIN or AVG would do the same thing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources