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!
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 | ---------------------
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.
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
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.
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'
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.