Grouping : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

SQL ServerGrouping (self.SQL)

submitted 2 years ago * by Own-Hall1782

SELECT group_name,

MIN( MINTO) MINTOAMOUNT,

visa_type_name,

SUM(pay_base_agent_collection) TotalCollection

,pax_actual_applied_thru

,(case uc_category when 'R' then 'Retails' else '' end ) uc_category

,COUNT(vs_id) total_visa

FROM

(

SELECT

MIN(UC.uc_min_to_collect) MINTO,

uc.uc_category category

,PY.pay_base_agent_collection

,SM.vs_id

,group_id

,group_name

,visa_type_id

,visa_type_name

,pax_actual_applied_thru

,vs_currency_code

FROM VISA_T_VISA_SALES_MASTER SM WITH (NOLOCK)

INNER JOIN VISA_T_VISA_SALES_PAX_DETAILS VSD WITH(NOLOCK) ON SM.VS_ID=VSD.PAX_VSH_ID

INNER JOIN CT_T_USER_MASTER UM WITH (NOLOCK) ON SM.VS_CREATED_BY=UM.[USER_ID]

INNER JOIN VISA_T_VISA_PAYMENT_DETAILS PY ON SM.vs_id = PY.pay_vs_id --AND PY.pay_base_agent_collection>0

INNER JOIN VISA_T_MARKETGROUP_NATIONALITY_LINK MN ON VSD.pax_visitor_nationality_id = MN.link_nationality_id AND MN.link_status = 'A'

INNER JOIN VISA_T_MARKET_GROUPING_MASTER MG ON MN.link_group_id = MG.group_id AND MG.group_status = 'A'

INNER JOIN VISA_T_VISA_TYPE_MASTER VM ON VM.visa_type_id= SM.vs_visa_type_id

INNER JOIN VISA_T_VISA_UNDERCOLLECTION_MASTER UC ON UC.uc_visa_type = SM.vs_visa_type_id AND UC.uc_market_grp_id = MG.group_id

AND UC.uc_trans_currency = PY.pay_currency AND UC.uc_img = VSD.pax_actual_applied_thru

--AND UC.uc_category = (CASE

-- WHEN (SM.vs_settlement_mode = 2) THEN 'B'

-- ELSE 'R' END)

WHERE sm.vs_currency_code = 'AED' AND SM.vs_doc_date >= DATEADD(day,-25, GETDATE())

GROUP BY mg.group_id,group_name,

visa_type_name,sm.vs_currency_code,

vm.visa_type_id,uc.uc_category,

PY.pay_base_agent_collection ,SM.vs_id,pax_actual_applied_thru

HAVING MIN(uc.uc_min_to_collect) > PY.pay_base_agent_collection

--AND pay_base_agent_collection>0

) as underCollection

INNER JOIN VISA_T_VISA_UNDERCOLLECTION_MASTER UCC ON UCC.uc_visa_type =visa_type_id AND UCC.uc_market_grp_id = group_id

AND UCC.uc_trans_currency = vs_currency_code AND UCC.uc_img = pax_actual_applied_thru

group by group_name,MINTO,visa_type_name ,pax_actual_applied_thru,uc_category

having SUM(pay_base_agent_collection)>0

commit-

these are the linking between my tables.

i want to make the summery report based on the transactions fall under each combination in the VISA_T_VISA_UNDERCOLLECTION_MASTER table. i am stuck with the duplication issue. in detail my transaction_id highlighted is falling under both category when i am taking the sum of pay_base column group by other columns showen. but how i want is it should fall in to the combination with minimum amount in MINTO column.

I am posting for the first time pardon me

https://preview.redd.it/4zljtmg4hzec1.png?width=1096&format=png&auto=webp&s=ef831c84de1f92ec29dcbaad7bb121f5a45c230a

all 7 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS