SQL Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query Help

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2011-06-21 : 06:00:57

Hi I need to create a report. Here are the details

I have a table called SubCat that looks like this

SubCat ID
Network Support IT
Programmer IT
Accountant Finance
Bookkeeper Finance
Sales Rep Marketing
Sales Admin Marketing
Doctor Medical

etc etc etc

I then have another Table Called JobOrder

ID Candidate Fee Race Gender Category SubCat

1 Joe 1000 White Male IT Network Support
2 Soap 2300 Indian Female Financial Accountant
etc etc etc

I need a report (gridview) that when I select The Categort (IT or Financial etc) that I get the following report. It counts how many orders we Placed per subcategory / Gender / Race.

SubCategory Black Male Black Femal White Male White Female Indian Male Indian Female etc etc

Hope this makes sense??

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 07:02:14

What do you mean when you say report?
If that is reporting services or some other package then you will need a fixed resultset format - you will need to specify the number of columns and their headings.
If it's a query or something you have more control over (a .net grid) then you can get the values dynamically.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLNoob81
Starting Member

38 Posts

Posted - 2011-06-21 : 09:57:00

Hi, Sorry. I need to create a SQL statement that will display the following:

ID Candidate Fee Race Gender Category SubCat

SubCategory Black_Male Black_Female White_Male White_Female etc
Network Support 11 0 5 2
Programmer 4 4 7 9
DB Administrator 4 3 7 0
IT Support 45 5 54 2

Hope that helps?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 10:17:23

Try this - maybe it'll help you think about what you want.

select sc.SubCat
,BlackMale = sum(case when jo.Race = 'Black' and jo.Gender = 'Male' then 1 else 0 end)
,BlackFeMale = sum(case when jo.Race = 'Black' and jo.Gender = 'FeMale' then 1 else 0 end)
...
from SubCat sc
left join JobOrder jo
on sc.SubCat = jo.SubCat
group by sc.subcat

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources