Here is my code, I had to split it in three different codes, how can I combine these three into one, all come from same table.

code #1

select SUM(`Line_Total`) as Total,
    SUM(`_Revenue`) as Incr
     from `table 1`
     where `Month`='201909'

code #2

select SUM(`Channel`) as SKG
    from `table 1`
 where `Division`='Good'  and `Month`='201909'

code #3

select SUM(`Channel`) as Direct
  from `table 1`
  where `Division`='BAD' and `Month`='201909'

Please tell me how I can optimize the code and combine into 1 code only. as they all are in same table, but I dont know how to use multiple where clause with 3 sums

  • where Month='201909' Do you really think that month number (or name?) have 6 digits? – Akina Nov 5 '19 at 17:21
  • @Akina, thats how our Months dB is stored. Please tell me how I can combine these 3 code into one. – S.Y.K.J Nov 5 '19 at 17:22
  • Specify ONE DBMS. Show table's DDL. Show example data and the final result for that data. – Akina Nov 5 '19 at 17:24
  • Look in to IN clause: tutorialspoint.com/mysql/mysql-in-clause.htm – Rekamanon Nov 5 '19 at 17:27
  • final o/p : Total | Incr| SKG | Direct 200 | 145| 6658| 5556 for the month of September. I want my query to refresh every month – S.Y.K.J Nov 5 '19 at 17:28

Queries 2 and 3 read a subset of the rows already read by query 1 so you can easily combine them by using that as a base and moving the additional filters into CASE expressions.

SELECT SUM(`Line_Total`) AS Total,
       SUM(`_Revenue`)   AS Incr,
       SUM(CASE WHEN `Division` = 'Good' THEN `Channel` END) AS SKG,
       SUM(CASE WHEN `Division` = 'BAD' THEN `Channel` END) AS Direct
WHERE  `Month` = '201909'
  • Im not sure if I explained it correctly, Query 2& 3 are sum of channels and where condition is on Divisions. – S.Y.K.J Nov 5 '19 at 18:16
  • yes, that's what the query in my answer does, column SKG for example, will be the SUM(Channel) for rows meeting the combined WHERE and CASE expressions, i.e. equivalent to where Division='Good' and Month='201909' – Martin Smith Nov 5 '19 at 18:19
  • thanks it works !! – S.Y.K.J Nov 5 '19 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.