UNION SQL Query are not working

I am trying to create the query with UNION where I have two same tables but the condition in both tables are different. I am getting output as below structure

 inward
 ---------
 100  // inward
---------
 50  // outward

but I expect it will come like this below

inward  |  outward
 -----------------------
 100    |  50
------------------------

My Code (I am trying):

SELECT
`u`.`inward`,
`u`.`outward`
FROM
    (
    SELECT
        COALESCE(SUM(`qty`),
        0) AS `inward`
    FROM
        `table_1`
    WHERE
        `col_name` = '321' AND (
            `transaction_type` = 'SALES' OR `transaction_type` = 'ISSUE'
        ) AND `col_id` = '123'
    UNION
SELECT
    COALESCE(SUM(`qty`),
    0) AS `outward`
FROM
    `table_1`
WHERE
    `col_name` = '321' AND (
        `transaction_type` = 'PURCHASE' OR `trans_type` = 'ISSUE'
    ) AND `col_id` = '123'
) u

Please help to get the expected output in your suggested methods

UNION adds rows to the end of the set.
JOIN adds columns to the set.

(this is an oversimplification, but it gets the point across)

How I make it correct

SELECT
    CASE WHEN transaction_type = 'PURCHASE' THEN COALESCE(SUM(`qty`), 0) ELSE 0 END AS `inward`
    CASE WHEN transaction_type = 'SALES' THEN COALESCE(SUM(`qty`), 0) ELSE 0 END AS `outward`
FROM
    `table_1`
WHERE
    `col_name` = '321' AND (
        (`transaction_type` = 'PURCHASE' OR transaction_type = 'SALES' OR `trans_type` = 'ISSUE'
    ) AND `col_id` = '123'

That’s not going to work, Thallius, because it mixes aggregates and non-aggregates without a GROUP BY Clause

SELECT SUM(CASE WHEN transaction_type IN ( 'ISSUE','SALES' )
                THEN qty
                ELSE 0 END ) AS inward
     , SUM(CASE WHEN transaction_type IN ( 'ISSUE','PURCHASE' )
                THEN qty
                ELSE 0 END ) AS outward
  FROM table_1
 WHERE col_name = '321' 
   AND col_id   = '123'
   AND transaction_type IN ( 'ISSUE','SALES','PURCHASE' )

OFFTOPIC

hey, i just noticed it’s my “cake day” – Joined Aug 29, '14

that’s actually not true, i’ve been here much longer than Sitepoint’s current forum software iteration

sitepoint-bling (2)288×589 14.5 KB

Thanks, I forgot the SUM of course

actually, you didn’t

you had the SUM inside the CASE, when you should’ve had the CASE inside the SUM

Yes this happens if you try to help on a Monday

Why do you have “two same tables”?

there’s actually only one

I noticed that on mine yesterday, until your comment about the forum change (which I’d forgotten) I thought it was a big coincidence.

Off Topic:

As you’ve probably realised, late August 2014 was when members were imported from vBulletin to Discourse. Large numbers of members have join dates of 29th and 30th.

The only folk with earlier join dates are members who were staff at the time of the move, and had been testing Discourse prior to the migration. e.g.:

It’s a shame we couldn’t import original join dates, but apparently that wasn’t possible.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.