question

m_don avatar image
m_don asked

count rows according to given condition.

examples are given below in text file...please help me.. [link text][1] initial table:-in this table interest of the students is given in different different areas. Inplay InStudy InPolitics InLaw yes no no yes yes yes yes yes no no yes no no yes no no yes yes no no yes no no no no no no yes Final table:-we are counting the no of student in different-different areas of interest using sql queries as given in table below according to given sql database. interest No.OfStudent Inplay 4 InStudy 3 InPolitics 2 InLaw 3 [1]: /storage/temp/997-example.txt
oracleexecution-count
example.txt (552 B)
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
I put the file contents into the question Is this homework? Can you post what you've tried so far? You may be close already
1 Like 1 ·
KenJ avatar image KenJ commented ·
The question was tagged sql-server-2008, so I gave a sql server answer. The Oracle UNPIVOT statement looks very similar. You might try adjusting my query to look like the example on oracle-base: http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php I'll retag the question as Oracle
1 Like 1 ·
m_don avatar image m_don commented ·
i tried using simple sql queries. like select count(interest) from initialtable... but only work for one column...if we generalise it for other column too...it's not working...so please give me some suitable sql queries... please..
0 Likes 0 ·
m_don avatar image m_don commented ·
it's working for one column one by one but how will it work for all rows and column...i studied the msdn examples but i am not getting how to use in this given examples. I am not able to apply whatever u said in the last paragraph...so please give me the query atleast two row in final table. please help me out...
0 Likes 0 ·
KenJ avatar image KenJ commented ·
I've added a working example with two columns to my answer. I did have a syntax issue with the original sample and apologize for any confusion that may have caused.
0 Likes 0 ·
Show more comments

1 Answer

·
KenJ avatar image
KenJ answered
If you do count or sum queries for single columns, you will need a query for each column then UNION ALL to put them together into a single result set. This is an approach that I've seen used for SQL Server 2000 and older. Since you're on SQL 2008, there is some newer syntax you can use... When you turn rows into columns it's called PIVOT and when you turn columns into rows, like you want here, it's called UNPIVOT. MSDN has an UNPIVOT query example that is very similar to what you are after here - [ http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.105).aspx][1] here is a snippet to get you rolling: SELECT Interest, NumberOfStudents FROM (SELECT sum(case when InPlay='yes' then 1 else 0 end) as InPlay from YOUR_TABLE_NAME) p You can extend the sum() section for your other columns and add an UNPIVOT NumberOfStudents for Interest as in the MSDN example. If you have trouble getting the UNPIVOT put together, just post what you have and I can help you out with it. **Updated to add this working sample with two columns:** CREATE TABLE #StudentOptions (Inplay VARCHAR(3),InStudy VARCHAR(3),InPolitics VARCHAR(3),InLaw VARCHAR(3)) INSERT #StudentOptions VALUES ( 'yes', 'no', 'no', 'yes'), ( 'yes', 'yes', 'yes', 'yes'), ( 'no', 'no', 'yes', 'no'), ( 'no', 'yes', 'no', 'no'), ( 'yes', 'yes', 'no', 'no'), ( 'yes', 'no', 'no', 'no'), ( 'no', 'no', 'no', 'yes') SELECT Interest, NumberOfStudents FROM ( SELECT SUM(CASE WHEN Inplay='yes' THEN 1 ELSE 0 END) AS InPlay, SUM(CASE WHEN InStudy='yes' THEN 1 ELSE 0 END) AS InStudy FROM #StudentOptions ) sums UNPIVOT (NumberOfStudents FOR Interest IN (InPlay, InStudy) ) AS unpivoted; DROP TABLE #StudentOptions [1]: http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.105).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.