sql - Group by on multiple columns using Aggregate functions -

-

i had following table structure listed below

budhol    cocode    ben      obj    spare2  spare1  taskno  value   fieldnamenew     362103  36      362101  991003  null    ma1001  null    4516    613030     362103  36      362101  991003  null    ma1001  null    9088    613030     362103  36      362101  991003  null    ma1001  null    3387    613030     362103  36      362101  991003  null    ma1001  null    4026    613030

below required output

 budhol   cocode    ben      obj    spare2  spare1  taskno  value   fieldnamenew     362103  36      362101  991003  null    ma1001  null    21017   613030

i.e value should sumed if columns same i.e. sum of value column

below query have tried value not coming properly

select b.ben,        b.budhol,        b.cocode,        b.fieldnamenew,        b.obj,        b.spare1,        b.spare2,               sum(b.value)as value #temp4 (     select distinct a.ben,                     a.budhol,                     a.cocode,                     a.fieldnamenew,                     a.obj,                     a.value,                     a.spare2,                     a.spare1           #temp3     left join #temp3 t1 on a.ben = t1.ben         , a.budhol = t1.budhol         , a.cocode = t1.cocode         , a.obj = t1.obj         , a.spare1 = t1.spare1         , a.spare2 = t1.spare2         , a.fieldnamenew = t1.fieldnamenew ) b group b.ben,          b.budhol,          b.cocode,          b.obj,          b.fieldnamenew,          b.spare1,          b.spare2

based on write, simpler query work:

select b.ben, b.budhol, b.cocode, b.fieldnamenew, b.obj, b.spare1, b.spare2,               sum(b.value) value #temp4 #temp3 b group b.ben, b.budhol, b.cocode, b.fieldnamenew, b.obj, b.spare1, b.spare2;

Comments

Post a Comment

Popular posts from this blog

sequelize.js - Sequelize group by with association includes id -

-
so when requesting group sequelize follows: return models.workingcalendar .findall({ attributes: [ 'workingcalendar.periodid', 'workingcalendar.date', 'period.name' ], include: [ { model: models.period, attributes: [] } ], where: { getsudoid: currentgetsudo.id, unitplantid: unitplantid }, group: ['workingcalendar.periodid', 'workingcalendar.date', 'period.name'], }); sequelize run query: select [workingcalendar].[id], [workingcalendar].[periodid], [workingcalendar].[date], [period].[name] [workingcalendars] [workingcalendar] left outer join [periods] [period] on [wor...
Read more

delphi - Take screenshot in webcam using VFrames in Console Application -

-
i'm doing program take picture of webcam using delphi xe2 , vframes achieve this, problem have figured out, in graphic application works fine, when use unit in console application, returns me error saying first chance exception @ $76b6b727. exception class eaccessviolation message 'access violation @ address 004a271b in module 'console.exe'. read of address 00000260'. process console.exe (3676) my unit : unit webcam; interface uses sysutils, windows, vcl.imaging.jpeg, vcl.graphics, vsample, vframes, classes; type twebcam = class private procedure newvideoframeevent(sender: tobject; width, height: integer; dataptr: pointer); public constructor create; destructor destroy; override; procedure capture_webcam(take_name: string); end; var web_image: tvideoimage; name_screen: string; implementation constructor twebcam.create; begin inherited create; end; destructor twebcam.destroy; begin inherited destroy; end; ...
Read more

ubuntu - Executors lost when starting pyspark in YARN client mode -

-
i able run pyspark in yarn client mode in laptop , trying setup in laptop. however, time can't running. when try start pyspark in yarn client mode, gives me following error. using dynamic resource allocation, have set spark_executor_memory less yarn container memory. using hadoop 2.6.4, spark 1.6.1, ubuntu 15.10 is possible error due network issues? 16/06/12 01:49:34 info scheduler.dagscheduler: executor lost: 1 (epoch 0) in [1]: 16/06/12 01:49:34 info cluster.yarnclientschedulerbackend: disabling executor 1. 16/06/12 01:49:34 info storage.blockmanagermasterendpoint: trying remove executor 1 blockmanagermaster. 16/06/12 01:49:34 info storage.blockmanagermasterendpoint: removing block manager blockmanagerid(1, 192.168.2.16, 37900) 16/06/12 01:49:34 error client.transportclient: failed send rpc 9123554941984942265 192.168.2.16/192.168.2.16:47630: java.nio.channels.closedchannelexception java.nio.channels.closedchannelexception 16/06/12 01:49:34 info storage.blockmanagermast...
Read more