sql - How to use distinct in a grouped query? -

-

i created view couple tables. each employee able work stores. each invoice have 1 product or more one. want show, store, employee, individual product total sales, total transaction (which means total invoice number), total of invoices.
here example table:

create table performanceview ([store] varchar(6), [employee] varchar(5), [product] varchar(8), [productprice] int,[invoiceno] int, [invoicetotal] int);   insert performanceview ([store], [employee], [product], [productprice], [invoiceno], [invoicetotal]) values ('store1', 'empl1', 'product1', 577.2, 42152, 676.2), ('store1', 'empl1', 'product1', 99, 42152, 676.2), ('store1', 'empl1', 'product2', 20, 41969, 20), ('store1', 'empl1', 'product2', 25, 41649, 25), ('store1', 'empl2', 'product2', 300, 43112, 300), ('store1', 'empl2', 'product2', 450, 42440, 450), ('store1', 'empl2', 'product3', 226, 41666, 2040.14), ('store1', 'empl2', 'product3', 282.17, 42232, 1678.5), ('store1', 'empl2', 'product3', 934.67, 41666, 2040.14), ('store1', 'empl2', 'product3', 1264.56, 42232, 1678.5), ('store1', 'empl2', 'product4', 49.81, 41666, 2040.14), ('store1', 'empl2', 'product4', 683.32, 41666, 2040.14), ('store1', 'empl2', 'product4', 0, 42232, 1678.5), ('store1', 'empl2', 'product4', 81.92, 42232, 1678.5), ('store1', 'empl2', 'product4', 146.34, 41666, 2040.14), ('store1', 'empl2', 'product4', 49.85, 42232, 1678.5), ('store1', 'empl2', 'product5', 30, 43110, 30), ('store1', 'empl3', 'product1', 172.5, 42875, 172.5), ('store3', 'empl4', 'product2', 51, 42801, 51), ('store3', 'empl4', 'product2', 51, 42488, 51), ('store3', 'empl4', 'product2', 80.002, 42463, 80.002), ('store3', 'empl4', 'product2', 22, 42559, 22), ('store3', 'empl4', 'product2', 20, 42963, 20), ('store3', 'empl4', 'product2', 35, 42436, 35), ('store3', 'empl4', 'product2', 107, 43014, 107), ('store3', 'empl4', 'product2', 59, 42615, 59), ('store3', 'empl4', 'product2', 110, 42025, 110), ('store3', 'empl4', 'product2', 89, 42137, 148), ('store3', 'empl4', 'product2', 50, 42565, 100), ('store3', 'empl4', 'product2', 59, 42137, 148), ('store3', 'empl4', 'product5', 25, 42565, 100), ('store3', 'empl4', 'product5', 25, 42565, 100), ('store3', 'empl5', 'product2', 22, 42295, 44), ('store3', 'empl5', 'product2', 22, 42295, 44), ('store3', 'empl5', 'product2', 439, 41723, 439), ('store3', 'empl5', 'product2', 197, 42267, 197), ('store3', 'empl5', 'product2', 150, 41780, 150), ('store3', 'empl5', 'product2', 539, 42280, 539), ('store3', 'empl5', 'product2', 63, 41689, 63), ('store3', 'empl5', 'product2', 269, 42274, 269), ('store3', 'empl5', 'product2', 134, 41731, 134), ('store2', 'empl3', 'product1', 0, 43076, 0), ('store2', 'empl3', 'product1', 149.49, 42557, 666.73), ('store2', 'empl3', 'product1', 0, 43076, 0), ('store2', 'empl3', 'product1', 0, 42917, 0), ('store2', 'empl3', 'product1', 61.01, 42557, 666.73), ('store2', 'empl3', 'product1', 389.4, 42557, 666.73), ('store2', 'empl3', 'product1', 66.84, 42557, 666.73), ('store2', 'empl3', 'product1', 0, 43076, 0), ('store2', 'empl3', 'product1', 15.42, 41650, 15.42), ('store2', 'empl3', 'product1', 0, 43088, 0), ('store2', 'empl3', 'product1', 0, 42920, 0), ('store2', 'empl3', 'product6', 0, 43076, 0);

i have tried code;

select  store, employee, product, sum(productprice) producttotal , sum(invoicetotal) invoicetotal, count(distinct invoiceno) transactions  performanceview  group store,employee,product

but invoice total wrong because of sums same invoices have.
can check in here http://sqlfiddle.com/#!3/e419f/6
have tried on clause did not give same view.

your problem view holds single product sales (one line per piece of product sold), have redundantly invoice invoice amount it. when grouping store, employee , product, count invoice amount many-fold. must first group store, employee, product and invoiceno, , final group:

select    store,   employee,   product,   sum(producttotal) producttotal,   sum(invoicetotal) invoicetotal,   count(*) transactions (   select      store,     employee,     product,     sum(productprice) producttotal,     min(invoicetotal) invoicetotal   performanceview    group store, employee, product, invoiceno ) pre_aggregate group store, employee, product;

here sql fiddle: http://sqlfiddle.com/#!3/e419f/32.


Comments

Post a Comment

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

-
i'm trying plot frequency characteristic equation using ezplot, matlab gives following warning, "contour not rendered non-finite zdata". have used command plot frequency equations warning , plot display empty , not change axis range well. can please help. appreciated. here's code i'm using. % transfer matrix case-i, thin rotor clear all; clc; ei = 1626; l = 0.15; m = 0.44108; = 2.178*10^-4; i_p = 2.205*10^-5; itr = 0.24; i_pr = 0.479; syms p n; f = [1 l*1i l^2/(2*ei)*1i l^3/(6*ei); 0 1 l/ei -l^2/(2*ei)*1i; 0 0 1 -l*1i; 0 0 0 l]; p = [ 1 0 0 0; 0 1 0 0; 0 -it*p^2+i_p*n*p 1 0; -m*p^2 0 0 1]; p_r = [1 0 0 0; 0 1 0 0; 0 -itr*p^2+i_pr*n*p 1 0; -m*p^2 0 0 1]; = f*p*f*p*f*p*f; b = p_r*f*p*f*p*f; r = a(1,2)/a(1,4); a12_p = 0; a22_p = a(2,2)-r*a(2,4); a32_p = a(3,2)-r*a(3,4); a42_p = a(4,2)-r*a(4,4); ap(2,2) = a22_p; ap(3,2) = a32_p; ap(4,2) = a42_p; ap(4,4) = 1; c = b*ap; m = [c(3,2) c(3,4); c(4,2) c(4,...
Read more

delphi - Indy UDP Read Contents of Adata -

-
i using indy udp server read string of data. however, not know how work adata parameter. can around converting binary using bytetobin function below , convert hex using bintohex1 . feel stupid , works slow. know how can directly results without 2 conversions? thanks!! here codes: procedure tform1.idudpserver1udpread(athread: tidudplistenerthread; const adata: tidbytes; abinding: tidsockethandle); var buf: tidbytes; buffer: string; data_received: string; begin if bytestostring(adata) <> 'hello' begin buffer := hextostring('00'); setlength(buf, length(buffer)); copytidstring(buffer, buf, 0); abinding.sendto(abinding.peerip, abinding.peerport, buf); data_received := bintohex1(bytetobin(adata[1]) + bytetobin(adata[2]) + bytetobin(adata[3]) + bytetobin(adata[4]) + bytetobin(adata[5]) + bytetobin(adata[6]) + bytetobin(adata[7]) + bytetobin(adata[8]) + bytetobin(adata[9]) + bytetobin(adata[10]) + bytetobin(adata[11...
Read more

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -

-
any ideas when firefox implement lengthadjust andtextlength?..... working svg application. lengthadjust , textlength working fine in chrome not working in fire fox . <svg viewbox = "0 0 500 300" version = "1.1"> <defs> <path id="s3" d="m 10,90 q 100,15 200,70" /> </defs> <g> <text font-size = "20"> <textpath xlink:href="#s3" textlength="205"> short text</textpath> </text> <use xlink:href="#s3" fill="none" stroke="black" stroke-width="1"/> </g> </svg> this implemented in firefox text elements (not textpath or tspan) in case that's ok though can move attribute text element. <svg viewbox = "0 0 500 300" version = "1.1"> <defs> ...
Read more