Need Help in SQL Query - 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

Need Help in SQL Query

Author

Topic

marcusalie
Starting Member

3 Posts

Posted - 2011-04-01 : 00:31:17

Hi all

I have a FIXEDASSET table with quite a number of columns in particular 'PC_TYPE', 'DEPARTMENT'

This table has about 50 over records with PC_TYPE column filled with "desktop" and "laptop"

how to i do a query for counting number of laptop and desktop for respective departments

Department | Laptop | Desktop
Mgmt 2 3
IT 3 5

my code:
SELECT (count(PC_TYPE) as" Number of Desktop" FROM FixedAsset where PC_TYPE='desktop ') , select (count(PC_TYPE) as "Number of laptop",PC_USER_DEPARTMENT from fixedasset where PC_Type='laptop')
group by PC_USER_DEPARTMENT;

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-01 : 01:44:54

[code]
SELECT PC_USER_DEPARTMENT,
COUNT(CASE WHEN PC_TYPE = 'desktop' THEN PC_TYPE ELSE NULL END) AS [Number of Desktop],
COUNT(CASE WHEN PC_TYPE = 'laptop' THEN PC_TYPE ELSE NULL END) AS [Number of Laptop],
FROM FixedAsset
GROUP BY PC_USER_DEPARTMENT
[/code]

Vaibhav T

If I cant go back, I want to go fast...

marcusalie
Starting Member

3 Posts

Posted - 2011-04-01 : 01:49:32

quote:
Originally posted by vaibhavktiwari83

SELECT PC_USER_DEPARTMENT,
COUNT(CASE WHEN PC_TYPE = 'desktop' THEN PC_TYPE ELSE NULL END) AS [Number of Desktop],
COUNT(CASE WHEN PC_TYPE = 'laptop' THEN PC_TYPE ELSE NULL END) AS [Number of Laptop],
FROM FixedAsset
GROUP BY PC_USER_DEPARTMENT

Vaibhav T

hi, thanks for your input. i running the query using MS ACCESS and it give me an error

syntax error (Missing operator) in query expression 'COUNT(CASE WHEN PC_TYPE = 'desktop' THEN PC_TYPE ELSE NULL END)'

If I cant go back, I want to go fast...

marcusalie
Starting Member

3 Posts

Posted - 2011-04-01 : 01:57:49

Error

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-01 : 02:05:35

This is SQL Server Forum.
Post your question in any MS Access forum for ex. - www.accessforums.net
to get the expected answer.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources