Stuck on 2 queries - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Stuck on 2 queries

Author

Topic

Maccaman
Starting Member

16 Posts

Posted - 2009-10-17 : 07:14:47

Hello,
I was wondering if someone could help write a query.

I have the following table titled 'Fish_data'

Month Fish.No Size.Cat Alive
JANUARY 1 A Yes
JANUARY 2 A Yes
JANUARY 3 A Yes
JANUARY 4 A Yes
JANUARY 5 A Yes
JANUARY 6 A Yes
JANUARY 7 A Yes
JANUARY 8 A Yes
JANUARY 9 A Yes
JANUARY 10 A Yes
FEBRUARY 1 A No
FEBRUARY 2 A No
FEBRUARY 3 A Yes
FEBRUARY 4 A Yes
FEBRUARY 5 A Yes
FEBRUARY 6 B Yes
FEBRUARY 7 B Yes
FEBRUARY 8 B Yes
FEBRUARY 9 B Yes
FEBRUARY 10 B Yes
MARCH 1 A No
MARCH 2 A No
MARCH 3 B Yes
MARCH 4 B Yes
MARCH 5 B Yes
MARCH 6 B Yes
MARCH 7 B Yes
MARCH 8 B Yes
MARCH 9 B No
MARCH 10 B No

which describes the size category for 10 individual fish and if they are alive in a certain month. I want to create 2 queries that I just don't know how to create yet. In the first query, I want to count the number of fish in each category in each month, but when a fish is dead then that particular fish's category becomes 'D'. For example, in January fish number 1 would be category 'A', but in February fish number 1 would be in category 'D' not 'A' as it appears in the data. So from this data set, the query would return:

Month Category Category.Count
January A 10
January B 0
January D 0
February A 3
February B 5
February D 2
March A 0
March B 6
March D 4

The second query is a lot more difficult (I think anyway). Ideally this query would return the number of movements from each category in one month, to another or the same category in the next month. So from this data I would like to have a query that produces the following results:

FromMonth ToMonth FromSize ToSize Transitions
January February A A 5
January February A B 3
January February A D 2
January February B A 0
January February B B 0
January February B D 0
January February D A 0
January February D B 0
January February D D 0
February March A A 0
February March A B 3
February March A D 0
February March B A 0
February March B B 3
February March B D 2
February March D A 0
February March D B 0
February March D D 2

I am a novice SQL user so I don't know how complicated it would be to create such a query, but any help would be greatly appreciated.
Thanks,
Maccaman

"I know the human being and fish can co-exist peacefully" - George W. Bush.
Obviously Mr Bush has never been fishing with me :)

Maccaman
Starting Member

16 Posts

Posted - 2009-10-26 : 01:21:20

Okay,
I have the first query.....

SELECT Month, Cat, Count(*) AS Cnt
FROM
(SELECT Month, CASE WHEN Alive = 'No'
THEN 'D'
ELSE Cat END AS Cat
FROM Fish_data) AS fish
GROUP BY Month, Cat

But im really stuck on the second query....Does anyone have any suggestions? Perhaps even creating a view to add columns to the table and then writing a query on top of that to achieve this?
Thanks to anyone in advance who can help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-26 : 02:06:48

just post in http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3265b10f-1c38-4bcf-81b9-f06152b30b52

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources