Thread: SQL Query Help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    3
    Rep Power
    0

    SQL Query Help


    Hello. I'm a developer who has done object oriented programming but I'm fairly new to SQL and I'm looking for a bit of help with a query.

    Essentially, I have a table in MS Access 2007 which contains information about servers. I am attempting to do is get a count of the different types of servers that were put into production by date.

    For an example:
    ||Server Type||Server Date||
    |Physical|9/14/2011|
    |Physical|9/14/2011|
    |Virtual||9/14/2011|
    |Physical|9/20/2011|
    |Virtual|9/22/2011|

    Should appear something like:
    ||Date||Physical||Virtual||
    |9/14/2011|2|1|
    |9/20/2011|1|0|
    |9/22/2011|0|1|

    What I've tried is something along the lines of:
    Code:
    SELECT impl_date AS Server_Date, (SELECT COUNT (type) FROM Tracker 
        WHERE type = "Virtual") AS Virtual_count, (SELECT COUNT (type) FROM Tracker
        WHERE type = 'Physical') AS Physical_count
    FROM Tracker
    GROUP BY impl_date;
    Which returns the total value of the counts for each date, instead of the counts for that date only.

    I wouldn't be surprised if there was a simple thing I was missing as again, I'm not used to SQL, but any help would be greatly appreciated.

    Thank you,
    - JD
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Code:
    SELECT impl_date AS Server_Date
         , COUNT(CASE WHEN type = 'Virtual'
                      THEN 'humpty' END) AS Virtual_count
         , COUNT(CASE WHEN type = 'Physical'
                      THEN 'dumpty' END) AS Physical_count
      FROM Tracker
    GROUP 
        BY impl_date;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    oops, wait a sec... you said MS Access...
    Code:
    SELECT impl_date AS Server_Date
         , COUNT(IIF(type='Virtual','humpty',NULL)) AS Virtual_count
         , COUNT(IIF(type='Physical','dumpty',NULL)) AS Physical_count
      FROM Tracker
    GROUP 
        BY impl_date;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    3
    Rep Power
    0
    r937,

    It worked perfectly. Thank you very much for your help!

IMN logo majestic logo threadwatch logo seochat tools logo