performance - Efficient use of SQL GROUP BY, SUM, COUNT -

-

i have table of products sales may follows:

product   | amount   | ptype    | pdate       p1        | 1.00     | sale     | 01/01 p1        | 2.00     | base     | 01/02 p2        | 1.50     | sale     | 02/03 p3        | 5.25     | base     | 10/10

and build table shows 1 product per row, sum of amounts, if product unique show type else show type 'var', if product unique show date else show date null. result follows:

product   | total    | ptype    | pdate       p1        | 3.00     | var      | (null) p2        | 1.50     | sale     | 02/03 p3        | 5.25     | base     | 10/10

i accomplishing result need doing following: 

select distinct product ,(select sum(amount) t b group b.product having a.product = b.product ) total ,(select case when count(*) = 1 a.ptype  else 'var' end t b group b.product having a.product = b.product) ptype ,(select case when count(*) = 1 a.pdate  else null end t b group b.product having a.product = b.product) pdate t

but know if there more efficient way accomplishes same result.

there no need use form of subquery or inline view. depending on sophistication of database engine, constructs negatively impact performance.

here requested, , should reliably give result single scan of table on primitive sql engine.

select product,        sum(amount) amount,        case when count(*)=1 min(ptype) else 'var' end ptype,        case when count(*)=1 min(pdate) else null end pdate   t  group product

the following not requested, think may closer looking for. reports ptype var or pdate null if there multiple distinct values making aggregate.

i've added pcount column can still identify singlet aggregates, if both ptype , pdate not nulll.

select product,        sum(amount) amount,        count(*) pcount,        case when count(distinct ptype)=1 min(ptype) else 'var' end ptype,        case when count(distinct pdate)=1 min(pdate) else null end pdate   t  group product

Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more