?
Solved

Conditional Group by

Posted on 2010-09-19
6
Medium Priority
?
850 Views
Last Modified: 2012-05-10
Hello
i have a a table mytable has a data as follows
---------------------------------------------------------------------------------------
ACC_no        | Tr_Date                    | Doc_No             | Doc_TY   line_No     | AMT
-----------------------------------------------------------------------------------------
12272              01-09-2010               12                           01         05              100
12272              05-09-2010               12                           01         06                20
12272              06-09-2010               12                           04         01              150
12272              07-09-2010               12                           02         01                  5
12272              07-09-2010               12                           04         01               110
12272              07-09-2010               12                           02         17                  90

What i need is to group by with a condition "When Doc_Ty =01 >>>> then group Doc_TY, When Doc_ty='04' >>>> then group by TR_DT , otherwise DONT GROUP)
0
Comment
Question by:ali_alannah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33711991
hi..

apart from grouping column, other columns are going 2 aggregate function?

else all columns will going to come in group by.

you can achieve it by

select columnlist
from table
group by case Doc_Ty when '01' then Doc_TY else when '04' then TR_DT end
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33712004

sory sory.....

my query is wrong.

how can u do conditional grouping when same result set having both values
0
 
LVL 3

Expert Comment

by:daddotnet
ID: 33712076
You can create a table to receive the result sets of 3 different queries, those being filtered using a WHERE clause for your 3 grouping types, something like:

select
ACC_no,
Tr_Date,
Doc_No,
Doc_TY,
min(line_No),
sum(AMT)
into #t_mytable
from mytable
where Doc_Ty='01'
group by
ACC_no,
Tr_Date,
Doc_No,
Doc_TY

insert into #t_mytable
select
ACC_no,
Tr_Date,
min(Doc_No),
min(Doc_TY),
min(line_No),
sum(AMT)
from mytable
where Doc_ty='04'
group by
ACC_no,
Tr_Date

insert into #t_mytable
select
ACC_no,
Tr_Date,
Doc_No,
Doc_TY,
line_No,
AMT
from mytable
where Doc_ty not in ('01','04')

select * from #t_mytable

drop table #t_mytable

Good luck,
DdN
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Expert Comment

by:fahadalihashmi
ID: 33712144
i think this would help this is a solution of a problem if bc_smtrtype is K1 AND A ACCNO NUMBER HAS OCCURED 1 THEN ONLY IT SHOULD BE COUNTED.

 Select *

  From (Select Bc_Sconn_Id,

               Bc_Saccno,

               Count(*) All_Mtr,

               Count(Case

                       When bc_smtrtype = 'K1' Then

                        1

                     End) K1_Mtr

          From Ind.Bs_Bill_Calreading C

         Where Bc_Sconn_Id In (Select Bc_Sconn_Id

                                 From Ind.Bs_Bill_Calreading

                                Where bc_smtrtype = 'K1')

           And bc_izone In (43, 44)

           And bc_strf in

               ('T39', 'T40', 'T41', 'T42', 'T43', 'T44', 'T66', 'T67')

         Group By Bc_Sconn_Id, Bc_Saccno)

Where All_Mtr = 1 And K1_Mtr = 1
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 33712560
you basically write the 3 separate queries for each situation and use UNION (ALL) to join them into one result set.select ACC_no ,Tr_Date ,Doc_No , Doc_TY ,  line_No , AMT from mytable  where Doc_ty not in (''01' .'04')Union  select ACC_no ,Max(Tr_Date) ,max(Doc_No) , '01' , max( line_No) ,sum( AMT) from mytable  where Doc_ty = ''01'  group by Acc_noUnion  select ACC_no ,Tr_Date ,max(Doc_No) , '04' , max( line_No) ,sum( AMT) from mytable  where Doc_ty = ''04'  group by Acc_no,tr_dateOrder by Acc_no,tr_date
0
 

Author Closing Comment

by:ali_alannah
ID: 33714401
Perfect
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question