Multiple Counts In One SQL Statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Multiple Counts In One SQL Statement

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Multiple Counts In One SQL Statement

    Hi,

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

    tblTrans
    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
    Posts
    4
    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
    Posts
    2
    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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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