asked on

i need help for query

$sql="
SELECT
s.survey_id AS 'Inspection ID',
s.license_number As 'License Number',
s.name_pos AS 'Name of POS',
s.address_pos AS 'Address of POS',
s.updated_at As 'Inspection Date',
s.updated_at As 'Inspection Date',
t.name As 'Town Name',
c.name As 'Districts Name',
s.gps_location As 'Location',
sum(a.option_value) As 'Final Scoring',
a.option_value As 'Remarks'
FROM ".DB_PREFIX . "survey s
INNER JOIN ".DB_PREFIX."town t
on t.town_id=s.town_id
INNER JOIN ".DB_PREFIX."city c
on c.city_id=s.city_id
INNER JOIN ".DB_PREFIX."answer a
on a.survey_id=s.survey_id WHERE a.question_id=371";

i want sum of option_value WHERE option_id!=0 in join query and a.option_value As 'Remarks' WHERE a.question_id=371";

from same table

Pawan Kumar

Please add these lines..

,SUM( CASE WHEN option_id!=0 THEN a.option_value ELSE 0 END ) option_value_SUM_Zero
,CASE WHEN a.question_id=371 THEN 'Remarks' ELSE option_value END option_value

Also you have not presented full query... Do you have input data and expected output?

PortletPaul

Well I would like to help, but as your data model isn't something known to me it would largely be a guess.
Do you have sample data (per table, not from that query) and the expected results ?

If you are wanting a sum() you will need a GROUP BY clause, but on line 12 below you also seem to want the same column that you sum to be output. Those 2 things are incompatible.

SELECT
      s.survey_id         AS 'Inspection ID'
    , s.license_number    AS 'License Number'
    , s.name_pos          AS 'Name of POS'
    , s.address_pos       AS 'Address of POS'
    , s.updated_at        AS 'Inspection Date'
    , s.updated_at        AS 'Inspection Date'
    , t.name              AS 'Town Name'
    , c.name              AS 'Districts Name'
    , s.gps_location      AS 'Location'
    , SUM(a.option_value) AS 'Final Scoring'
    , a.option_value      AS 'Remarks'
FROM dbo.survey s
INNER JOIN dbo.town t ON t.town_id = s.town_id
INNER JOIN dbo.city c ON c.city_id = s.city_id
INNER JOIN dbo.answer a ON a.survey_id = s.survey_id
WHERE a.question_id = 371

Open in new window

this is not a solution, just a re-format of the query in the question.

Pawan Kumar

Update if option_value is INT/BIGINT/NUMERIC

,SUM( CASE WHEN option_id!=0 THEN a.option_value ELSE 0 END ) option_value_SUM_Zero
,CASE WHEN a.question_id=371 THEN 'Remarks' ELSE CAST(option_value AS VARCHAR(MAX)) END option_value

Hamdan Shafiq

ASKER

we can not change option_value

Hamdan Shafiq

ASKER

plz add in query

SELECT
      s.survey_id         AS 'Inspection ID'
    , s.license_number    AS 'License Number'
    , s.name_pos          AS 'Name of POS'
    , s.address_pos       AS 'Address of POS'
    , s.updated_at        AS 'Inspection Date'
    , s.updated_at        AS 'Inspection Date'
    , t.name              AS 'Town Name'
    , c.name              AS 'Districts Name'
    , s.gps_location      AS 'Location'
    , SUM(a.option_value) AS 'Final Scoring'
    , a.option_value      AS 'Remarks'
FROM dbo.survey s
INNER JOIN dbo.town t ON t.town_id = s.town_id
INNER JOIN dbo.city c ON c.city_id = s.city_id
INNER JOIN dbo.answer a ON a.survey_id = s.survey_id
WHERE a.question_id = 371

Open in new window

Pawan Kumar

Please try this-

--

SELECT
      s.survey_id         AS 'Inspection ID'
    , s.license_number    AS 'License Number'
    , s.name_pos          AS 'Name of POS'
    , s.address_pos       AS 'Address of POS'
    , s.updated_at        AS 'Inspection Date'
    , s.updated_at        AS 'Inspection Date'
    , t.name              AS 'Town Name'
    , c.name              AS 'Districts Name'
    , s.gps_location      AS 'Location'
    , SUM(a.option_value) AS 'Final Scoring'
    , a.option_value      AS 'Remarks'
	,SUM( CASE WHEN option_id <> 0  THEN a.option_value ELSE 0 END ) 
       AS option_value_SUM_Zero	 
FROM dbo.survey s
INNER JOIN dbo.town t ON t.town_id = s.town_id
INNER JOIN dbo.city c ON c.city_id = s.city_id
INNER JOIN dbo.answer a ON a.survey_id = s.survey_id
WHERE a.question_id = 371

--

Open in new window

PortletPaul

@pawan there needs to be a group by, and a.option_value (line 14 now) cannot be summed if it also output as a column....

PortletPaul

@Hamdan Shafiq

you have asked for a column you call "Remarks" it is , a.option_value AS 'Remarks'

In the next line you try to SUM(a.option_value)

You cannot "sum" text so how is a.option_value something that can be "remarks" and something that can be summed?

what is the "data type" of column a.option_value ?

Hamdan Shafiq

ASKER

in option id we have text and value both , if option id is zero (371 ) it will take remarks otherwise it will do sum of that option id not equal to zero.

Pawan Kumar

OK.
Please use this-

--

SELECT
      s.survey_id         AS 'Inspection ID'
    , s.license_number    AS 'License Number'
    , s.name_pos          AS 'Name of POS'
    , s.address_pos       AS 'Address of POS'
    , s.updated_at        AS 'Inspection Date'
    , s.updated_at        AS 'Inspection Date'
    , t.name              AS 'Town Name'
    , c.name              AS 'Districts Name'
    , s.gps_location      AS 'Location'
    , SUM(a.option_value) OVER() AS 'Final Scoring'
    , a.option_value      AS 'Remarks'
	, CASE WHEN option_id = 0 THEN 'Remarks' ELSE CAST(SUM( CASE WHEN option_id <> 0 THEN a.option_id END ) AS VARCHAR(MAX)) END 
		AS option_value_SUM_Zero	 
FROM dbo.survey s
INNER JOIN dbo.town t ON t.town_id = s.town_id
INNER JOIN dbo.city c ON c.city_id = s.city_id
INNER JOIN dbo.answer a ON a.survey_id = s.survey_id
WHERE a.question_id = 371

--

Open in new window

Hamdan Shafiq

ASKER

Fatal error: Uncaught exception 'Exception' with message 'Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '() AS 'Final Scoring' , a.option_value AS 'Remarks' , CASE WHEN option' at line 11<br />Error No: 1064<br />SELECT s.survey_id AS 'Inspection ID' , s.license_number AS 'License Number' , s.name_pos AS 'Name of POS' , s.address_pos AS 'Address of POS' , s.updated_at AS 'Inspection Date' , s.updated_at AS 'Inspection Date' , t.name AS 'Town Name' , c.name AS 'Districts Name' , s.gps_location AS 'Location' , SUM(a.option_value) OVER() AS 'Final Scoring' , a.option_value AS 'Remarks' , CASE WHEN option_id = 0 THEN 'Remarks' ELSE CAST(SUM( CASE WHEN option_id <> 0 THEN a.option_id END ) AS VARCHAR(MAX)) END AS option_value_SUM_Zero FROM su_survey s INNER JOIN su_town t ON t.town_id = s.town_id I in /home/farmacy/public_html/system/library/db/mysqli.php on line 40

Pawan Kumar

sorry updated..

--

SELECT
      s.survey_id         AS 'Inspection ID'
    , s.license_number    AS 'License Number'
    , s.name_pos          AS 'Name of POS'
    , s.address_pos       AS 'Address of POS'
    , s.updated_at        AS 'Inspection Date'
    , s.updated_at        AS 'Inspection Date'
    , t.name              AS 'Town Name'
    , c.name              AS 'Districts Name'
    , s.gps_location      AS 'Location'
    , SUM(a.option_value) AS 'Final Scoring'
    , a.option_value      AS 'Remarks'
	, CASE WHEN option_id = 0 THEN 'Remarks' ELSE CAST(SUM( CASE WHEN option_id <> 0 THEN a.option_id END ) AS VARCHAR(MAX)) END 
		AS option_value_SUM_Zero	 
FROM dbo.survey s
INNER JOIN dbo.town t ON t.town_id = s.town_id
INNER JOIN dbo.city c ON c.city_id = s.city_id
INNER JOIN dbo.answer a ON a.survey_id = s.survey_id
WHERE a.question_id = 371

--

Open in new window

Hamdan Shafiq

ASKER

file attach here
export_survey.php

Pawan Kumar

<?php
class ModelSurveyExportSurvey extends Model {

	public function export($data = array()) {

		$output = '';

		$fp = fopen('php://temp', 'r+');

		fputs($fp, $bom = ( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

		//$sql="
//	SELECT 
//s.survey_id AS 'Inspection ID',
//s.license_number As 'License Number',
//s.name_pos AS 'Name of POS',
//s.address_pos AS 'Address of POS',
//s.updated_at As 'Inspection Date',
//s.updated_at As 'Inspection Date',
//t.name As 'Town Name',
//c.name As 'Districts Name',
//s.gps_location As 'Location',
//a.question_id As 'Final Scoring',
//a.option_value As 'Remarks'
//FROM  ".DB_PREFIX . "survey s
//INNER JOIN ".DB_PREFIX."town t
//on t.town_id=s.town_id
//INNER JOIN ".DB_PREFIX."city c
//on c.city_id=s.city_id 
//INNER JOIN ".DB_PREFIX."answer a
//on a.survey_id=s.survey_id WHERE a.question_id=371";
//	
		$sql="SELECT
      s.survey_id         AS 'Inspection ID'
    , s.license_number    AS 'License Number'
    , s.name_pos          AS 'Name of POS'
    , s.address_pos       AS 'Address of POS'
    , s.updated_at        AS 'Inspection Date'
    , s.updated_at        AS 'Inspection Date'
    , t.name              AS 'Town Name'
    , c.name              AS 'Districts Name'
    , s.gps_location      AS 'Location'
    , SUM(a.option_value) AS 'Final Scoring'
    , a.option_value      AS 'Remarks'
	, CASE WHEN option_id = 0 THEN 'Remarks' ELSE CAST(SUM( CASE WHEN option_id <> 0 THEN a.option_id END ) AS CHAR(100)) END 
		AS option_value_SUM_Zero	 
FROM ".DB_PREFIX."survey s
INNER JOIN ".DB_PREFIX."town t ON t.town_id = s.town_id
INNER JOIN ".DB_PREFIX."city c ON c.city_id = s.city_id
INNER JOIN ".DB_PREFIX."answer a ON a.survey_id=s.survey_id
WHERE a.question_id=371";
		
         if(!empty($data['filter_name_pos'])) {
			$sql .= " AND s.name_pos LIKE '" . $this->db->escape($data['filter_name_pos']) . "%'";
		}
		if (isset($data['filter_license_number']) && $data['filter_license_number'] !== '') {
			$sql .= " AND s.license_number= '" .$data['filter_license_number'] . "'";
		}
		if (isset($data['filter_category']) && $data['filter_category'] !== '') {
			$sql .= "s.category_id= '" . (int)$data['filter_category'] . "'";
		}
		
		if (!empty($data['filter_created_at'])) {
            $sql .= " AND DATE(s.created_at) = DATE('" . $this->db->escape($data['filter_created_at']) . "')";
        }
		if (isset($data['filter_pharmacist']) && $data['filter_pharmacist'] !== '') {
			$sql .= " AND s.pharmacist_id= '" . (int)$data['filter_pharmacist'] . "'";
		}
		if (isset($data['filter_state_id']) && $data['filter_state_id'] !== '') {
			$sql .= " AND s.state_id= '" . (int)$data['filter_state_id'] . "'";
		}
		if (isset($data['filter_city_id']) && $data['filter_city_id'] !== '') {
			$sql .= " AND s.city_id= '" . (int)$data['filter_city_id'] . "'";
		}
		if (isset($data['filter_town_id']) && $data['filter_town_id'] !== '') {
			$sql .= " AND s.town_id= '" . (int)$data['filter_town_id'] . "'";
		}
		 $sort_data = array(
            's.survey_id',
            's.created_at'
        );
		
		 $sql .= " ORDER BY s.updated_at";
		  $sql .= " DESC";
		 $sql .= " LIMIT 300";
		
		
		$results = $this->db->query($sql);

		$row = $results->row;

		fputcsv($fp, array_keys($row));

		rewind($fp);

		$output .= fgets($fp);

		$default_store = $this->config->get('config_name');

		foreach ($results->rows as $result) {

			if (is_null($result['Store'])) {
				$result['Store'] = $default_store;
			}

			rewind($fp);
			fputcsv($fp, $result);
			rewind($fp);
			$output .= fgets($fp);
		}

		return $output;
	}

}

Open in new window

Hamdan Shafiq

ASKER

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(MAX)) END
AS option_value_SUM_Zero
FROM su_survey s
INNER JOIN s' at line 13

Pawan Kumar

Please try this-
changed that code.

<?php
class ModelSurveyExportSurvey extends Model {

	public function export($data = array()) {

		$output = '';

		$fp = fopen('php://temp', 'r+');

		fputs($fp, $bom = ( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

		//$sql="
//	SELECT 
//s.survey_id AS 'Inspection ID',
//s.license_number As 'License Number',
//s.name_pos AS 'Name of POS',
//s.address_pos AS 'Address of POS',
//s.updated_at As 'Inspection Date',
//s.updated_at As 'Inspection Date',
//t.name As 'Town Name',
//c.name As 'Districts Name',
//s.gps_location As 'Location',
//a.question_id As 'Final Scoring',
//a.option_value As 'Remarks'
//FROM  ".DB_PREFIX . "survey s
//INNER JOIN ".DB_PREFIX."town t
//on t.town_id=s.town_id
//INNER JOIN ".DB_PREFIX."city c
//on c.city_id=s.city_id 
//INNER JOIN ".DB_PREFIX."answer a
//on a.survey_id=s.survey_id WHERE a.question_id=371";
//	
		$sql="SELECT
      s.survey_id         AS 'Inspection ID'
    , s.license_number    AS 'License Number'
    , s.name_pos          AS 'Name of POS'
    , s.address_pos       AS 'Address of POS'
    , s.updated_at        AS 'Inspection Date'
    , s.updated_at        AS 'Inspection Date'
    , t.name              AS 'Town Name'
    , c.name              AS 'Districts Name'
    , s.gps_location      AS 'Location'
    , SUM(a.option_value) AS 'Final Scoring'
    , a.option_value      AS 'Remarks'
	, CASE WHEN option_id = 0 THEN 'Remarks' ELSE CAST(SUM( CASE WHEN option_id <> 0 THEN a.option_id END ) AS CHAR(100)) END 
		AS option_value_SUM_Zero	 
FROM ".DB_PREFIX."survey s
INNER JOIN ".DB_PREFIX."town t ON t.town_id = s.town_id
INNER JOIN ".DB_PREFIX."city c ON c.city_id = s.city_id
INNER JOIN ".DB_PREFIX."answer a ON a.survey_id=s.survey_id
WHERE a.question_id=371";
		
         if(!empty($data['filter_name_pos'])) {
			$sql .= " AND s.name_pos LIKE '" . $this->db->escape($data['filter_name_pos']) . "%'";
		}
		if (isset($data['filter_license_number']) && $data['filter_license_number'] !== '') {
			$sql .= " AND s.license_number= '" .$data['filter_license_number'] . "'";
		}
		if (isset($data['filter_category']) && $data['filter_category'] !== '') {
			$sql .= "s.category_id= '" . (int)$data['filter_category'] . "'";
		}
		
		if (!empty($data['filter_created_at'])) {
            $sql .= " AND DATE(s.created_at) = DATE('" . $this->db->escape($data['filter_created_at']) . "')";
        }
		if (isset($data['filter_pharmacist']) && $data['filter_pharmacist'] !== '') {
			$sql .= " AND s.pharmacist_id= '" . (int)$data['filter_pharmacist'] . "'";
		}
		if (isset($data['filter_state_id']) && $data['filter_state_id'] !== '') {
			$sql .= " AND s.state_id= '" . (int)$data['filter_state_id'] . "'";
		}
		if (isset($data['filter_city_id']) && $data['filter_city_id'] !== '') {
			$sql .= " AND s.city_id= '" . (int)$data['filter_city_id'] . "'";
		}
		if (isset($data['filter_town_id']) && $data['filter_town_id'] !== '') {
			$sql .= " AND s.town_id= '" . (int)$data['filter_town_id'] . "'";
		}
		 $sort_data = array(
            's.survey_id',
            's.created_at'
        );
		
		 $sql .= " ORDER BY s.updated_at";
		  $sql .= " DESC";
		 $sql .= " LIMIT 300";
		
		
		$results = $this->db->query($sql);

		$row = $results->row;

		fputcsv($fp, array_keys($row));

		rewind($fp);

		$output .= fgets($fp);

		$default_store = $this->config->get('config_name');

		foreach ($results->rows as $result) {

			if (is_null($result['Store'])) {
				$result['Store'] = $default_store;
			}

			rewind($fp);
			fputcsv($fp, $result);
			rewind($fp);
			$output .= fgets($fp);
		}

		return $output;
	}

}

Open in new window

Hamdan Shafiq

ASKER

plz check result is wrong
inspection_2017-12-11_13-29-14.csv
Screenshot-2017-12-11-18.30.57.png

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.