SQL Data retrieval QUERY DOUBT

Hi All,

I need one help towards execution of a query as foll.

I am retreiving the data basing on a query using group by clause (groupcode). I also want to retrieve the data by groupcode followed by sortcode. each group there is a sortcode 1 to 4 and  so I am able to retrieve the data basing on group but not able to get the data in groupcode and increment by sortcode.

select a.activityid,ss.subscalecode, SN.name, sum(a.AnswerValue) as ANSWERVAL, ss.sortcode,convert(varchar,a.answerdate, 106)
from Answer a, subscale ss, subscalename sn
where a.questioncode in (select questioncode from question where subscalecode = ss.subscalecode) and a.consumerid = 100 and SN.subscalecode = ss.subscalecode
group by a.activityid, ss.subscalecode,sn.name,convert(varchar,a.answerdate, 106) ,ss.sortcode, ss.scalecode


Activity      SubScaleCode    Name            Ans        SortCode          Date               ScaleCode

2      DAY01REL               Relationships      2      2      25 Oct 2002          DAY01
2      DAY01RSK          Risk Behaviors      2      4      25 Oct 2002          DAY01
2      DAY01SOC                  Social      2      3       25 Oct 2002         DAY01
2      DAY01WAM           Work       3      1      25 Oct 2002          DAY01

I want the SortCode also in an order along with the group of DAY01

Thanks,
Kven
kvenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danblakeCommented:
If the problem is the ordering of the data, which is what it sounds like here add the order by clause at the end to sort the results after you have performed your group by statement like so:

select a.activityid,ss.subscalecode, SN.name, sum(a.AnswerValue) as ANSWERVAL, ss.sortcode,convert(varchar,a.answerdate, 106)
from Answer a, subscale ss, subscalename sn
where a.questioncode in (select questioncode from question where subscalecode = ss.subscalecode) and a.consumerid = 100 and SN.subscalecode = ss.subscalecode
group by a.activityid, ss.subscalecode,sn.name,convert(varchar,a.answerdate, 106) ,ss.sortcode, ss.scalecode
order by ScaleCode, SortCode
0
kvenAuthor Commented:
Hi danblake,

Thank you for the response.. Here I need the data in Group wise and in each group the sortcode should be groupspecific. Here I have added one more example of fetching group specific data.
The query fetches me more than one groups of data that is FEN01, SEP01 etc.. so if I do a Order by clause directly then I will get the results by sortcode across all the groups. But I want the sort by each group.
for DAY01 it should gmme an 1-4 order and next for FEN01 it should get me the data in order again. Pls hv a look at the details below.

Activity      SubScaleCode    Name            Ans        SortCode          Date               ScaleCode

2     DAY01REL              Relationships     2                  1       25 Oct 2002          DAY01
2     DAY01RSK          Risk Behaviors      2                   2       25 Oct 2002          DAY01
2     DAY01SOC                 Social          2                   3        25 Oct 2002          DAY01
2     DAY01WAM           Work                3                   4         25 Oct 2002          DAY01

2     FEN01REL              Name1              2                  1        20 Oct 2002         FEN01
2     FEN01RSK             Name2              2                  2       20 Oct 2002           FEN01
2     FEN01SOC             Name3              2                  3       20 Oct 2002          FEN01
2     FEN01WAM           Name14             3                  4       20 Oct 2002          FEN01

Pls let me know if u have any more doubts..

Thanks,
Kven
0
danblakeCommented:
Kven,

The two order by items should perform what you want...
Ensure that your scalecodes are sorted and ordered such as:
day01 , day01 , day 01...... fen01, fen01, fen01

and then once that order by has been performed perform the sortcode ordering:
1,2,3,4

just like as the example as given.
The sorting should be group specific as specified in this way grouped in output as specified to generate your sum data, then just ordered as rqd.
If the ordering is in reverse just add the ASC clause and this will ensure 1->N ordering.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
danblakeCommented:
Ok, a quick question why the grade B ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.