Development SQL help...?

Discussion in 'Software' started by jopers1986, 3 Nov 2008.

  1. jopers1986

    jopers1986 Minimodder

    Joined:
    7 Jan 2005
    Posts:
    559
    Likes Received:
    4
    Right, this is probably going to be really simple, but i really can't seem to work it out, and it's been doing my head in!

    I'm trying to run a query which will show me the totals in a field from a look up, for example in a staff table, how many males and how many females.

    Say we have tbl.staff with the fields ID, First name, Surname, and Gender.

    What SQL statement would produce a result showing 2 columns, one called male, and one called female, then 1 row showing the totals for each gender!

    The winner can have a high 5, and maybe a little belly rub!

    Thanks guys!
     
  2. Firehed

    Firehed Why not? I own a domain to match.

    Joined:
    15 Feb 2004
    Posts:
    12,574
    Likes Received:
    16
    I don't think there's any way to get it as two columns, but I think that "SELECT gender, COUNT(*) FROM staff GROUP BY gender" should do it for you - will yield:
    Code:
    ---------------------
    | gender | count(*) |
    ---------------------
    | M      | 4        |
    | F      | 7        |
    ---------------------
     
  3. Fumduck

    Fumduck Have torch, will melt.

    Joined:
    5 Aug 2008
    Posts:
    162
    Likes Received:
    7
    Something like this will work for you.

    SELECT
    COUNT(a.ID) as [Female],
    ( SELECT COUNT(b.ID)
    FROM tbl.staff b
    WHERE b.Gender = 'Male')
    as [Male]
    FROM tbl.staff a
    WHERE a.Gender = 'Female'

    A better solution would be

    SELECT
    Gender,
    COUNT(ID)
    FROM tbl.staff
    GROUP BY Gender

    But that returns 2 rows instead of 2 columns.

    A third solution, but the most painful would be to do a SQL PIVOT, but you need to be running SQL Server 2005 or above.

    WOW, got to apply my real job to a forum here. :eyebrow:
     
  4. jopers1986

    jopers1986 Minimodder

    Joined:
    7 Jan 2005
    Posts:
    559
    Likes Received:
    4
    haha cheers guys! i thought i bashed one out a while back on a course, but thinkin my memory's not showing me what i really did!

    Have got SQL server 2005 somewhere so will give it a go!

    Thanks :D
     
  5. Firehed

    Firehed Why not? I own a domain to match.

    Joined:
    15 Feb 2004
    Posts:
    12,574
    Likes Received:
    16
    May be a bit different in MSSQL than MySQL which is what my example is based off, but the SQL syntax doesn't get too varied between them until you get into complex joins and such.
     
  6. BentAnat

    BentAnat Software Dev

    Joined:
    26 Jun 2008
    Posts:
    7,230
    Likes Received:
    219
    Firehed - that'll work in MSSql just the same... :)
     
  7. yakyb

    yakyb i hate the person above me

    Joined:
    10 Oct 2006
    Posts:
    2,064
    Likes Received:
    36
    not had much luck witht he term myself as i dont thinks its properly implemented yet but there is a see if you can get this result using 'PIVOT OVER'
     
  8. Fumduck

    Fumduck Have torch, will melt.

    Joined:
    5 Aug 2008
    Posts:
    162
    Likes Received:
    7
    Don't bother with PIVOT

    Pivot has proven to be much more expensive for the server to process. Usually around 50% longer to execute the query than simply using subselects to make a cross-tab.

    A better solution would be to use a query to retrieve all rows from the table into Excel (or the spreadsheet of your choice) and creating a pivot table from the raw data. The basic rule of thumb we use here is "Queries return data, applications analyze it."

    The less analysis yor queries perform, the quicker you will be able to produce usefully information for the executive staff, or your professors. :)
     

Share This Page