[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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
0
kven
Asked:
kven
  • 3
1 Solution
 
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
 
danblakeCommented:
Ok, a quick question why the grade B ?
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now