CODEWITHSUNDEEP
phpmysqlsql
nainy
nainy

Reputation: 540

SQL query with CASE in SELECT doesn't work

I have this part of a larger complex query:

SELECT SUM(pir.priceChange) AS CASE 
WHEN (SUM(pir.priceChange) > 0) THEN sellSum 
WHEN (SUM(pir.priceChange) < 0) THEN priceSum 
END

Whenever I try to pass it on to MySQL (MyISAM) via PDO, it throws me an exception:

Syntax error or access violation: 1064 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 '(SELECT SUM(pir.priceChange) AS CASE WHEN (SUM(pir.priceChange) > 0) THEN sellSu' at line 17

I imagine it has something to do with the fact that I'm trying to use a CASE statement incorrectly. Would really like to know what's exactly wrong, thanks in advance.

EDIT: My full query (edited without any cases):

SELECT  pi.id AS id,
pi.type AS type,
pi.parValue AS rating,
pi.duration AS time,
countAmt.amt AS amtSold,
IFNULL(pii.pChange,
0) AS priceTotal,
0) AS costTotal,
IFNULL(pii.selfPrice,
0) AS costPrice,
(IFNULL(pii.sellSum,
0) - IFNULL(pii.selfPrice,
0)) AS profit,
countClose.amtClosed AS amtClosed
FROM salon.paymentInstrument as pi
(SELECT SUM(pir.priceChange) AS pChange,
SUM(pir.costChange) AS cChange,
(IFNULL(pir.priceChange,
0) - IFNULL(pir.costChange,
0)) AS selfPrice,
pii.paymentInstrumentID
FROM salon.paymentInstrumentRegister as pir
JOIN salon.paymentInstrumentItem as pii ON pir.paymentInstrumentItemID = pii.id
WHERE pir.date >= '2014-04-11'
AND pir.date <= '2014-04-25'
GROUP BY pii.paymentInstrumentID 
) as pii
(SELECT pii.paymentInstrumentID,
COUNT(*) AS amtClosed
FROM salon.paymentInstrumentItem as pii
WHERE pii.annulTime >= 2014-04-11
AND pii.annulTime <= 2014-04-25
AND pii.status <> "active"
GROUP BY pii.paymentInstrumentID 
) as countClose
(SELECT pii.paymentInstrumentID,
COUNT(*) AS amt
FROM salon.paymentInstrumentItem as pii
WHERE pii.startTime >= 2014-04-11
AND pii.startTime <= 2014-04-25
GROUP BY pii.paymentInstrumentID 
) as countAmt

Sorry for improper formatting, it's generated dynamically with PHP. I have a feeling i'm making a mistake somewhere else in the query.

Upvotes: 0

Views: 654

Answers (2)

Raging Bull
Raging Bull

Reputation: 18747

Try this way:

SELECT CASE WHEN SUM(pir.priceChange)>0 THEN SUM(pir.priceChange) END as SumPrice,
       CASE WHEN SUM(pir.priceChange)<0 THEN SUM(pir.priceChange) END as priceSum
FROM TableName

An example in SQL Fiddle.

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 1549

Try this:

SELECT CASE 
WHEN (SUM(pir.priceChange) > 0) THEN sellSum 
WHEN (SUM(pir.priceChange) < 0) THEN priceSum 
END

Upvotes: 0

Related Questions