Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2017
    Posts
    1

    Unanswered: Where clause in a count COMMAND

    I am writing a Sybase SQL command, grouping 3 variables, then SUMming and COUNTing the number of instances and $ amount.

    I would like the second COUNT command to be filtered:

    SELECT A,B,C,
    COUNT(*), -- the count of all vouchers
    COUNT(where paid='Y'), -- the number of vouchers that have been paid
    SUM(amount_paid)
    GROUP by A,B,C
    ORDER by A,B,C;


    Is this possible, or will I have to run the command twice (with and without the WHERE clause) and merge the results?

    Thanks!

  2. #2
    Join Date
    Mar 2017
    Posts
    3

    Lightbulb

    how about giving this a try?

    create table BILLING (a int, b char(1), c int, paid char(1), amount_paid int)
    go

    INSERT INTO BILLING SELECT 1,'b',1,'Y',1000
    INSERT INTO BILLING SELECT 2,'c',1,'N',12
    INSERT INTO BILLING SELECT 4,'d',1,'Y',43
    INSERT INTO BILLING SELECT 6,'e',1,'Y',62
    INSERT INTO BILLING SELECT 7,'b',1,'N',5
    INSERT INTO BILLING SELECT 9,'b',1,'N',22
    INSERT INTO BILLING SELECT 7,'d',1,'Y',3
    INSERT INTO BILLING SELECT 10,'d',1,'Y',11
    INSERT INTO BILLING SELECT 10,'d',1,'Y',222
    go

    select a, b, c
    ,(select count(1) from billing) all_vouchers
    ,count(paid) paid_vouchers
    ,sum(amount_paid) total_paid_per_a_b_c
    from billing
    where paid = 'Y'
    group by a,b,c,paid
    order by a,b,c
    go

    expected result:
    a b c all_vouchers paid_vouchers total_paid_per_a_b_c
    ----------- -- ----------- ------------ ------------- --------------------
    1 b 1 9 1 1000
    4 d 1 9 1 43
    6 e 1 9 1 62
    7 d 1 9 1 3
    10 d 1 9 2 233

  3. #3
    Join Date
    Oct 2017
    Posts
    1
    Hi,

    Not sure if I understood your question correctly. Please try with having clause and let us know if this works.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •