• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

output in single row - union query

In the following query the OUTPUT "USAGE" is returned in 2 rows because of union.

But I need output as "SUM(USAGE)" in single row as output from both the queries. How do i achieve this ?

// FOLLOWING CAN BE USED FOR BOTH COVER CHARGE AND DISCOUNT BY PASSING RELEVANT PARAMETERS TO QUERY
JOIN OTHER TR TABLES AS NEEDED

SELECT SUM(POINTOFSALEBILLDETAIL.NETAMOUNT) AS USAGE
  FROM POINTOFSALEBILL
       INNER JOIN POINTOFSALEBILLDETAIL
          ON POINTOFSALEBILL.POINTOFSALEBILL_ID =
                POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID
       INNER JOIN LEDGER
          ON LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID
       INNER JOIN POINTOFSALE
          ON POINTOFSALE.POINTOFSALE_ID = POINTOFSALEBILL.POINTOFSALE_ID
       INNER JOIN MEMBER
          ON MEMBER.MEMBER_ID = POINTOFSALEBILL.MEMBER_ID
       INNER JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                MEMBER.CATEGORYSUBGROUP_ID
       INNER JOIN TRANSACTIONMODE
          ON TRANSACTIONMODE.TRANSACTIONMODE_ID =
                POINTOFSALEBILL.TRANSACTIONMODE_ID
 WHERE     POINTOFSALEBILL.FLAG = 0
       AND LEDGER.ISCOVERCHARGE = 1
       AND LEDGER.ISDISCOUNT = 1
       AND POINTOFSALE.ISBANQUET = 2
       AND MEMBER.ISCOVERCHARGE = 1
       AND MEMBER.ISCONTROVERSIAL = 2
       AND CATEGORYSUBGROUP.ISCOVERCHARGE = 1
       AND TRANSACTIONMODE.ISCREDITTRANSACTIONMODE = 1

UNION ALL
SELECT SUM (GUESTREGISTRATIONDETAIL.NETAMOUNT) AS USAGE
  FROM GUESTREGISTRATION
       INNER JOIN GUESTREGISTRATIONDETAIL
          ON GUESTREGISTRATION.GUESTREGISTRATION_ID =
                GUESTREGISTRATIONDETAIL.GUESTREGISTRATION_ID
       INNER JOIN LEDGER
          ON LEDGER.LEDGER_ID = GUESTREGISTRATIONDETAIL.LEDGER_ID
       INNER JOIN MEMBER
          ON MEMBER.MEMBER_ID = GUESTREGISTRATION.MEMBER_ID
       INNER JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                MEMBER.CATEGORYSUBGROUP_ID
       INNER JOIN TRANSACTIONMODE
          ON TRANSACTIONMODE.TRANSACTIONMODE_ID =
                GUESTREGISTRATION.TRANSACTIONMODE_ID
 WHERE     GUESTREGISTRATION.FLAG = 0
       AND LEDGER.ISCOVERCHARGE = 1
       AND LEDGER.ISDISCOUNT = 1
       AND MEMBER.ISCOVERCHARGE = 1
       AND MEMBER.ISCONTROVERSIAL = 2
       AND CATEGORYSUBGROUP.ISCOVERCHARGE = 1
       AND TRANSACTIONMODE.ISCREDITTRANSACTIONMODE = 1
0
GRChandrashekar
Asked:
GRChandrashekar
1 Solution
 
slightwv (䄆 Netminder) Commented:
select sum(usage) from
(
your select here
)
/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now