Outputting Issue

[ginga8]

I am trying to achieve the 2 outputs that I have attached. I am pulling the information from a database that has a few tables. My Query is as follows

 

SELECT gwa_properties.COMM_NM, gwa_samples.SAMPLE_NO, gwa_samples.VALUE, gwa_minerals.MINERAL_NAME, gwa_unit_codes.UNIT_CODE 
FROM test.gwa_properties as gwa_properties
Inner Join KEVIN.gwa_properties_samples as gwa_properties_samples ON gwa_properties.PID = gwa_properties_samples.FK_PID
Inner Join test.gwa_samples as gwa_samples ON gwa_properties_samples.FK_SAMPLE_NO = gwa_samples.SAMPLE_NO
Inner Join KEVIN.gwa_minerals as gwa_minerals ON gwa_minerals.ID = gwa_samples.FK_MINERAL_ID 
Inner Join test.gwa_unit_codes as gwa_unit_codes ON gwa_unit_codes.ID = gwa_samples.FK_MINERAL_ID
WHERE gwa_properties.COMM_NM = 'COMMUNITY1'

 

I have tried to do it with a multidimensional array but not having much luck. The rows outputted looks as follows

 

   

COMM_NM

   

SAMPLE_NO

   

VALUE

   

MINERAL_NAME

 

 

   

COMMUNITY1

   

333

   

+3.050

   

"Magnesium, dissolved"                           

 

 

   

COMMUNITY1

   

333

   

+17.800

   

"Sodium, dissolved"                             

 

 

   

COMMUNITY1

   

444

   

+2.300

   

"Magnesium, dissolved"                           

   

 

   

COMMUNITY1

   

444

   

+33.100

   

"Sodium, dissolved"                             

   

 

 

I need it to read that there was 2 unique sample numbers (based on the SAMPLE_NO) and then for each Minerals (Magnesium and Sodium) I need to do a min,max and average. to arrive at an output similar to the attached.

 

Any help would be appriciated

 

Thanks

 

 

 

[attachment deleted by admin]

[printf]

Could I see the real table scheme of both those tables... (because you could do it all in the query)

[ginga8]

Here it is

 

[attachment deleted by admin]

[printf]

That doesn't tell me anything, please provide the real create table scheme's so I can help you.

[ginga8]

Not sure I can get that info

[ginga8]

Will this work?

 

-- ----------------------------
-- Table structure for gwa_minerals
-- ----------------------------
CREATE TABLE `gwa_minerals` (
  `ID` varchar(255) DEFAULT NULL,
  `MINERAL_NAME` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

-- ----------------------------
-- Table structure for gwa_properties
-- ----------------------------
CREATE TABLE `gwa_properties` (
  `MAPINFO_ID` varchar(255) NOT NULL DEFAULT '',
  `STREET_NO` varchar(255) DEFAULT NULL,
  `STREET_NM` varchar(255) DEFAULT NULL,
  `COMM_NM` varchar(255) DEFAULT NULL,
  `COUNTY` varchar(255) DEFAULT NULL,
  `PID` varchar(255) DEFAULT NULL,
  `APT_NO` varchar(255) DEFAULT NULL,
  `LATITUDE` varchar(255) DEFAULT NULL,
  `LONGITUDE` varchar(255) DEFAULT NULL,
  `WATERSHED_NM` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`MAPINFO_ID`),
  KEY `PID` (`PID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

-- ----------------------------
-- Table structure for gwa_properties_samples
-- ----------------------------
CREATE TABLE `gwa_properties_samples` (
  `FK_PID` varchar(255) DEFAULT NULL,
  `FK_SAMPLE_NO` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

-- ----------------------------
-- Table structure for gwa_samples
-- ----------------------------
CREATE TABLE `gwa_samples` (
  `ID` varchar(255) NOT NULL DEFAULT '',
  `SAMPLE_NO` varchar(255) DEFAULT NULL,
  `FLAG` varchar(255) DEFAULT NULL,
  `VALUE` varchar(255) DEFAULT NULL,
  `FK_UNIT_CODE_ID` varchar(255) DEFAULT NULL,
  `FK_MINERAL_ID` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `SAMPLE_NO` (`SAMPLE_NO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

-- ----------------------------
-- Table structure for gwa_unit_codes
-- ----------------------------
CREATE TABLE `gwa_unit_codes` (
  `ID` varchar(255) DEFAULT NULL,
  `UNIT_CODE` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;