CODEWITHSUNDEEP
mysqlgroup-bysubstring
Saqib
Saqib

Reputation: 1129

Group by a Substring mysql not working

I want to use GROUP BY clause on a query in case IFF it's column TYPE contains [Sale], for that purpose I put the query below,

SELECT 
   PAYABLE_TYPE AS TYPE,
   CASE WHEN (SUBSTRING(PAYABLE_TYPE, 1,6) = '[Sale]') THEN PAYABLE_TYPE END AS T,
   PAYABLE_PARTICULAR AS PARTICULAR,
   DEBIT,
   CREDIT,
   PAYABLE_DATE AS DATE
FROM PAYABLES
WHERE PAYABLE_DATE >= $P{DateStart} && PAYABLE_DATE <= $P{DateEnd}
GROUP BY T

But it only returns me 2 lines at all, what I have is in the image below and the GREEN boxed line shows what I need to group. One more thing I need that grouped total to be sum up as well there. Thanks

enter image description here

Upvotes: 0

Views: 1355

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You want to group by the sales but leave the other rows intact. Let me assume that you have a unique id on each row that is a non-negative integer:

SELECT 
   PAYABLE_TYPE AS TYPE,
   CASE WHEN (SUBSTRING(PAYABLE_TYPE, 1,6) = '[Sale]') THEN PAYABLE_TYPE END AS T,
   max(PAYABLE_PARTICULAR) AS PARTICULAR,
   sum(DEBIT) as debit,
   sum(CREDIT) as credit,
   PAYABLE_DATE AS DATE
FROM PAYABLES
WHERE PAYABLE_DATE >= $P{DateStart} && PAYABLE_DATE <= $P{DateEnd}
GROUP BY PAYABLE_DATE, (case WHEN (SUBSTRING(PAYABLE_TYPE, 1,6) = '[Sale]') then -1 else id end);

Upvotes: 1

James King
James King

Reputation: 6365

You have no aggregate functions in the query. Maybe you are trying to do something like this?

SELECT TYPE, T, PARTICULAR, DATE, SUM(DEBIT), SUM(CREDIT) FROM
(SELECT 
   PAYABLE_TYPE AS TYPE,
   CASE WHEN (SUBSTRING(PAYABLE_TYPE, 1,6) = '[Sale]') THEN PAYABLE_TYPE END AS T,
   PAYABLE_PARTICULAR AS PARTICULAR,
   DEBIT,
   CREDIT,
   PAYABLE_DATE AS DATE
FROM PAYABLES
WHERE PAYABLE_DATE >= $P{DateStart} && PAYABLE_DATE <= $P{DateEnd})
GROUP BY TYPE, T, PARTICULAR, DATE

Upvotes: 0

Related Questions