Multiple Select Statement in MariaDB MySQL

Question

I have the following sql statement and its working fine to a certain extent in that if possible would like to make it better and also get a total of all the selects at end row. I also tried to make the WHERE in one statement but was not able it did not return the order I have done below.

Thanks & Regards

SELECT  
    coalesce(account, ' ') AS Account,
    sum(gl.credit - gl.debit) AS Balances
    FROM `tabGL Entry` AS gl 
    WHERE account LIKE 'INC%'
    GROUP BY account WITH ROLLUP
UNION ALL
SELECT 
    coalesce(account, ' ') AS Account,
    sum(gl.debit - gl.credit) AS Balances
    FROM `tabGL Entry` AS gl
    WHERE account LIKE 'DCOI%'
    GROUP BY account WITH ROLLUP
UNION ALL
SELECT 
    coalesce(account, ' ') AS Account,
    sum(gl.debit - gl.credit) AS Balances
    FROM `tabGL Entry` AS gl
    WHERE account LIKE 'DMC%'
    GROUP BY account WITH ROLLUP
UNION ALL
SELECT 
    coalesce(account, ' ') AS Account,
    sum(gl.debit - gl.credit) AS Balances
    FROM `tabGL Entry` AS gl
    WHERE account LIKE 'INFC%'
    GROUP BY account WITH ROLLUP
UNION ALL
SELECT 
    coalesce(account, ' ') AS Account,
    sum(gl.debit - gl.credit) AS Balances
    FROM `tabGL Entry` AS gl
    WHERE account LIKE 'IDEX%'
    GROUP BY account WITH ROLLUP

To clarify the results required

Inc item 1 ---- 100
Inc item 2 ---- 100
 Inc Total ----  200
DCOI item 1 ---- 100 
DCOI item 2 ---- 100 
 DCOI Total ----  200
DMC item 1 ---- 100 
DMC item 21 ---- 100 
 DMC Total ----  200
Total Inc-(DCOI+DMC+INFC+IDEX)

Try to reorganise your data so that you can use '=' instead of 'LIKE' — Strawberry, Aug 07 '17 at 06:22
`order by case when account LIKE 'INC%' then 1 when ... when account LIKE 'IDEX%' then 5 end` — jarlh, Aug 07 '17 at 06:58

score -1 · Answer 1 · answered Aug 07 '17 at 05:30

-1

You can use OR with multiple Like operator

  SELECT  
        coalesce(account, ' ') AS Account,
        sum(gl.credit - gl.debit) AS Balances
        FROM `tabGL Entry` AS gl 
        WHERE 
        account LIKE 'INC%'
        OR  LIKE 'DCOI%'
        OR  account LIKE 'DCOI%'
        OR account LIKE 'DMC%'
        OR account LIKE 'INFC%'
        OR account LIKE 'IDEX%'
        GROUP BY account WITH ROLLUP

answered Aug 07 '17 at 05:30

rupesh_padhye

1,355
2
13
25

thanks for the answer, I actually did try that as well and while I got the result it was not in the order that I needed it to be. – Said Aug 07 '17 at 05:39
https://stackoverflow.com/questions/3609166/mysql-order-by-like this answer may help – rupesh_padhye Aug 07 '17 at 06:09
If this is to work, it requires a little more thought – Strawberry Aug 07 '17 at 06:24

Multiple Select Statement in MariaDB MySQL

1 Answers1