DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009

    Multiple Counts In One SQL Statement


    I'm pretty brand new to SQL. I have the following DB

    ID (PriKey) | Account | Status | Email
    1 1234 failed a@a.com
    2 1234 complete a@a.com
    3 4987 failed a@a.com
    4 4321 complete a@a.com

    What I'm trying to do is count the number of different Accounts used, the number of Failed's and the number of Complete's all grouped by the Email. So it should look something like:

    Email | # Of Accounts | # of Failed Attempts | # of Complete Attempts
    a@a.com 3 2 2

    I've tried the below statement but I get ridiculous numbers.

    SELECT x.email,
    (SELECT DISTINCT Count(account) from tblTrans y WHERE y.email = x.email),
    (SELECT Count(*) from tblTrans y WHERE y.email = x.email and status="failed"),
    (SELECT Count(*) from tblTrans y WHERE y.email = x.email and status="complete")
    FROM tblTrans x
    GROUP BY email

    Any help would be greatly appreciated. Thanks!

  2. #2
    Join Date
    Feb 2009
    Try this,

    SELECT email,COUNT(account),SUM(CASE WHEN status='failed' THEN 1 ELSE 0 END), SUM(CASE WHEN status='complete' THEN 1 ELSE 0 END)
    FROM tblTrans
    GROUP BY email

  3. #3
    Join Date
    Apr 2009
    Thanks for your reply Rajesh. I completely forgot that I need this written for Access.

    SELECT email,COUNT(account), SUM(SWITCH(status='failed', 1)), SUM(SWITCH(status='complete', 1))
    FROM tblTrans
    GROUP BY email

    The only issue with the above statement is I need a DISTINCT COUNT of account. I know Access will not allow me to use COUNT (DISTINCT account). I know I'd have to use (SELECT DISTINCT account...) but I am not sure how it would be done.

Similar Threads

  1. SQL - WHERE clause with variable value
    By beecool in forum Database
    Replies: 10
    Last Post: 01-27-2010, 01:28 PM
  2. Multiple SQL servers, One which is mobile...
    By textony in forum Database
    Replies: 6
    Last Post: 03-20-2009, 12:31 PM
  3. How to send multiple SQL commands in one batch?
    By Inge Larsson in forum Database
    Replies: 0
    Last Post: 03-28-2003, 12:18 PM
  4. SQL Statement Logging
    By John Senford in forum Java
    Replies: 1
    Last Post: 09-26-2001, 11:41 AM
  5. Multiple SQL Alerts within a minute
    By Jonni Faiga in forum authorevents.vieira
    Replies: 1
    Last Post: 01-29-2001, 08:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.