sum, count, max, min bersamaan

Page 1 of 1

You cannot start a new topic
You cannot reply to this topic

sum, count, max, min bersamaan tentang group by, agregasi, STAT Rate Topic: 1 Votes

#1 aqge

Standard Member

Group: Members
Posts: 187
Joined: 02-September 10
Location:Indonesia

Posted 02 October 2010 - 02:56 AM

Agan-agan,
Suhu-suhu,
Guru-guru...

Bagaimana car mendapatkan

"select A.field1, A.field2, sum(B.harga), count(B.id), max(B.harga), min(B.harga)
from A
Join B.xx = A.xx "

dengan active record?

Telah saja coba dengan relation

public function relations()
	{
       return array(
                   'agregate'=>array(self::STAT,'B','A_id',
			'select'=>'sum(B.price) as Total, count(B.B_id) as jumlah,  
                                   max(B.price) as maks, min(B.price) as Min' )
                   )
    }

gak bisa

mohon diterawang...

Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik

#2 nasrul

Junior Member

Group: Members
Posts: 98
Joined: 14-April 09
Location:Sorowako - Indonesia

Posted 09 October 2010 - 02:46 PM

apa ada pesan error:

atau mungkin bisa dengan jalan mendefinisikan masing-masing agregate

public function relations()
{
	return array(
		'agregateSum'=>array(
			self::STAT,'B','A_id',
			'select'=>'sum(B.price)' 
		),
		'agregateCount'=>array(
			self::STAT,'B','A_id',
			'select'=>'count(B.B_id)' 
		),
		'agregateMax'=>array(
			self::STAT,'B','A_id',
			'select'=>'max(B.price)' 
		),
		'agregateMin'=>array(
			self::STAT,'B','A_id',
			'select'=>'min(B.price)' 
		),
   )
}

ada pun pada viewnya:

<?php
    $data = A::model()->findAll();
    foreach ($data as $dataItem)
    {
	echo $dataItem->id;
   		echo "Total = ".$dataItem->agregateSum;
   		echo "Count = ".$dataItem->agregateCount;
   		echo "Min = ".$dataItem->agregateMin;
   		echo "Max = ".$dataItem->agregateMax;
    }
?>

#3 junxiong

Advanced Member

Group: Members
Posts: 537
Joined: 21-June 10

Posted 10 October 2010 - 12:51 AM

apa mungkin karena belum pakai "group" ??
ini ada contoh yang dipakai, di thread sebelah

'rating' => array(self::STAT, 'Rating', 'walk_id',
                        'select' =>'ROUND(AVG(rating))',
                        'group' => 'walk_id',
                ),

“The most likely way for the world to be destroyed, most experts argue, is by accident. That’s where we come in; we’re computer professionals. We cause accidents.” - Nathaniel Borenstein

Yii Playground : Collaborative demo apps. You can join to improve it too!
My Team's Blog: In Indonesian.

#4 aqge

Standard Member

Group: Members
Posts: 187
Joined: 02-September 10
Location:Indonesia

Posted 18 October 2010 - 04:17 AM

nasrul, on 09 October 2010 - 02:46 PM, said:

apa ada pesan error:

atau mungkin bisa dengan jalan mendefinisikan masing-masing agregate

public function relations()
{
	return array(
		'agregateSum'=>array(
			self::STAT,'B','A_id',
			'select'=>'sum(B.price)' 
		),
		'agregateCount'=>array(
			self::STAT,'B','A_id',
			'select'=>'count(B.B_id)' 
		),
		'agregateMax'=>array(
			self::STAT,'B','A_id',
			'select'=>'max(B.price)' 
		),
		'agregateMin'=>array(
			self::STAT,'B','A_id',
			'select'=>'min(B.price)' 
		),
   )
}

ada pun pada viewnya:

<?php
    $data = A::model()->findAll();
    foreach ($data as $dataItem)
    {
	echo $dataItem->id;
   		echo "Total = ".$dataItem->agregateSum;
   		echo "Count = ".$dataItem->agregateCount;
   		echo "Min = ".$dataItem->agregateMin;
   		echo "Max = ".$dataItem->agregateMax;
    }
?>

terima kasih suhu, yang ini bisa...
dari awal saya memang tidak mencoba trik begini karena khawatir akan ada dua join sehingga selain merusak hasinya juga menyita resouresnya DB,

tapi hasilnya sih bener keluar tapi soal resourcesnya ane kurang paham nih... sebelum ada yang meng-konfirmasi trik diatas tidak boros resources, ane masih deg-degan makainya...

trim again bos nasrul,

Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik

#5 nasrul

Junior Member

Group: Members
Posts: 98
Joined: 14-April 09
Location:Sorowako - Indonesia

Posted 20 October 2010 - 12:54 AM

aqge, on 18 October 2010 - 04:17 AM, said:

terima kasih suhu, yang ini bisa... dari awal saya memang tidak mencoba trik begini karena khawatir akan ada dua join sehingga selain merusak hasinya juga menyita resouresnya DB, tapi hasilnya sih bener keluar tapi soal resourcesnya ane kurang paham nih... sebelum ada yang meng-konfirmasi trik diatas tidak boros resources, ane masih deg-degan makainya... trim again bos nasrul,

iya, saya juga berfikir demikian,

mungkin bisa pula melalui findAllBySql() http://www.yiiframew...AllBySql-detail

<?php
    $sql="
        SELECT
                A.id, 
                sum(B.price) as Total, 
                count(B.B_id) as jumlah, 
                max(B.price) as maks, 
                min(B.price) as Min
        FROM 
                A INNER JOIN B ON A.ID=B.A_ID
        ";

    $data = A::model()->findAllBySql($sql);
    foreach ($data as $dataItem)
    {
	echo $dataItem['id'];
   	echo "Total = ".$dataItem['Total'];
   	echo "Count = ".$dataItem['jumlah'];
   	echo "Min = ".$dataItem['Min'];
   	echo "Max = ".$dataItem['maks'];
    }
?>

#6 aqge

Standard Member

Group: Members
Posts: 187
Joined: 02-September 10
Location:Indonesia

Posted 20 October 2010 - 11:12 PM

nasrul, on 20 October 2010 - 12:54 AM, said:

iya, saya juga berfikir demikian,

mungkin bisa pula melalui findAllBySql() http://www.yiiframew...AllBySql-detail

<?php
    $sql="
        SELECT
                A.id, 
                sum(B.price) as Total, 
                count(B.B_id) as jumlah, 
                max(B.price) as maks, 
                min(B.price) as Min
        FROM 
                A INNER JOIN B ON A.ID=B.A_ID
        ";

    $data = A::model()->findAllBySql($sql);
    foreach ($data as $dataItem)
    {
	echo $dataItem['id'];
   	echo "Total = ".$dataItem['Total'];
   	echo "Count = ".$dataItem['jumlah'];
   	echo "Min = ".$dataItem['Min'];
   	echo "Max = ".$dataItem['maks'];
    }
?>

sepertinya emang harus carving sql sendiri ya bos, sementara ini keputusan apa pake relational AR atau carving sql bisa dengan mempertimbangkan kemungkinan banyaknya data...
go go Yii

Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik

#7 YII alvs

Junior Member

Group: Members
Posts: 53
Joined: 11-February 10

Posted 26 October 2010 - 11:40 PM

hasil nya di tampilkan dlm Cgrigview gimana bro :rolleyes:

#8 aqge

Standard Member

Group: Members
Posts: 187
Joined: 02-September 10
Location:Indonesia

Posted 28 October 2010 - 09:39 PM

nasrul, on 09 October 2010 - 02:46 PM, said:

apa ada pesan error:

atau mungkin bisa dengan jalan mendefinisikan masing-masing agregate

public function relations()
{
	return array(
		'agregateSum'=>array(
			self::STAT,'B','A_id',
			'select'=>'sum(B.price)' 
		),
		'agregateCount'=>array(
			self::STAT,'B','A_id',
			'select'=>'count(B.B_id)' 
		),
		'agregateMax'=>array(
			self::STAT,'B','A_id',
			'select'=>'max(B.price)' 
		),
		'agregateMin'=>array(
			self::STAT,'B','A_id',
			'select'=>'min(B.price)' 
		),
   )
}

ada pun pada viewnya:

<?php
    $data = A::model()->findAll();
    foreach ($data as $dataItem)
    {
	echo $dataItem->id;
   		echo "Total = ".$dataItem->agregateSum;
   		echo "Count = ".$dataItem->agregateCount;
   		echo "Min = ".$dataItem->agregateMin;
   		echo "Max = ".$dataItem->agregateMax;
    }
?>

menampilkannya di gridview mungkin begini gan...
tapi belum dicoba ya cuma main comot nih

<?php $this->widget('zii.widgets.CDetailView', array(
	'data'=>$data ,
	'attributes'=>array(
		
		array('name'=>'total','value'=>$data->agregateSum->s),
		array('name'=>'count','value'=>$data->agregateCount->s),
                array('name'=>'minimal','value'=>$data->agregateMin->s),
		array('name'=>'maximal','value'=>$data->agregateMax->s),
	),
)); ?>

Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik

#9 saebaryo

Junior Member

Group: Members
Posts: 74
Joined: 13-October 10
Location:jogjakarta - Indonesia

Posted 26 December 2010 - 05:14 AM

nasrul, on 20 October 2010 - 12:54 AM, said:

iya, saya juga berfikir demikian,

mungkin bisa pula melalui findAllBySql() http://www.yiiframew...AllBySql-detail

<?php
    $sql="
        SELECT
                A.id, 
                sum(B.price) as Total, 
                count(B.B_id) as jumlah, 
                max(B.price) as maks, 
                min(B.price) as Min
        FROM 
                A INNER JOIN B ON A.ID=B.A_ID
        ";

    $data = A::model()->findAllBySql($sql);
    foreach ($data as $dataItem)
    {
	echo $dataItem['id'];
   	echo "Total = ".$dataItem['Total'];
   	echo "Count = ".$dataItem['jumlah'];
   	echo "Min = ".$dataItem['Min'];
   	echo "Max = ".$dataItem['maks'];
    }
?>

kalo seperti code diatas, apa perlu didefinisikan terlebih dahulu ya, Total, jumlah, min dan maks nya.
soalnya ketika saya coba spt cara itu mesti ada pernyataan kalo field yg digunakan sebagai "as" itu bukan merupakan property salah satu tabel.

kalo saya baca di threadsebelah ada yg jawab spt ini

kodeart, on 30 May 2009 - 03:40 AM, said:

You don't have to declare all your properties. Do it as you always code in OOP fashion. It's interesting topic, lets see if this helps:

In your model

1. Declare some properties. One class property as a placeholder for dynamic fields, one array with your preferred field names and one for languages

2. The power of PHP5, magic methods. Now override __get() and __set()

3. Initialize your properties. This method will set all of them by language. It will create an array like this: 'strTextMk' = null, 'strTitleMk' => null ...>

Ok, now we have getter/setter for properties and a nice function to create them.

In your controller, or elsewhere

Also, you can isolate *global logic* in one model and extend other models from there. Override specific methods as needed. Go OOP

Probably it's not the best solution, but I hope it will help you somehow.

Cheers.

zaccaria, on 18 November 2010 - 02:31 AM, said:

You can achive with a bit of conditions:

$criteria= new CDbCriteria;
$criteria->select= ' SUM(`count`) as sum';
$criteria->join= 'INNER JOIN `orders` USING(`idorders`)';
$criteria->condition='`customer_company`=1';
orderDetail::model()->findAll($criteria);

In orderDetail you have also to add a property sum, in wich you will get the result.

mohon petunjuk dari suhu2 sekalian, ane gak paham maksudnya.

everything must standing on balance

#10 nasrul

Junior Member

Group: Members
Posts: 98
Joined: 14-April 09
Location:Sorowako - Indonesia

Posted 03 January 2011 - 01:16 PM

saebaryo, on 26 December 2010 - 05:14 AM, said:

kalo seperti code diatas, apa perlu didefinisikan terlebih dahulu ya, Total, jumlah, min dan maks nya.
soalnya ketika saya coba spt cara itu mesti ada pernyataan kalo field yg digunakan sebagai "as" itu bukan merupakan property salah satu tabel.
kalo saya baca di threadsebelah ada yg jawab spt ini
mohon petunjuk dari suhu2 sekalian, ane gak paham maksudnya.

menurut sy, tergantung arsitektur dbnya. biasanya untuk mysql butuh enclosed (`) untuk menandai object dalam database. sehingga sql harusnya menjadi:


$sql="
        SELECT
                `A`.`id`, 
                sum(`B`.`price`) as `Total`, 
                count(`B`.`B_id`) as `jumlah`, 
                max(`B`.price) as `maks`, 
                min(`B`.`price`) as `Minimum`
        FROM 
                `A` INNER JOIN `B` ON `A`.`ID`=`B`.`A_ID`
        ";

beda halnya denga sqlite, enclosed tidak diperlukan atau bahkan kadang dibutuhkan dengan quote (")
untuk kasus ini solve dari zaccaria mungkin yang terbaik:

zaccaria, on 18 November 2010 - 02:31 AM, said:

You can achive with a bit of conditions:

$criteria= new CDbCriteria;
$criteria->select= ' SUM(`count`) as sum';   //perhatikan tanda (`) sebagai penanda fieldnya
$criteria->join= 'INNER JOIN `orders` USING(`idorders`)';
$criteria->condition='`customer_company`=1';
orderDetail::model()->findAll($criteria);

In orderDetail you have also to add a property sum, in wich you will get the result.

sehingga jika disesuaikan dengan kasus kita harusnya menjadi

$criteria= new CDbCriteria;
$criteria->select= '`A`.`id`,sum(`B`.`price`) as Total,count(`B`.`B_id`) as jumlah, max(`B`.`price`) as maks,min(`B`.`price`) as minimum';
$criteria->join= 'INNER JOIN `B` USING(`A_ID`)';
//$criteria->condition='`customer_company`=1';      //pada kasus kita tidak ada kondisi
A::model()->findAll($criteria);

atau jika Kamu telah mendefinisikan relations A ke B pada model A

public function relations()
	{
		return array(
   						'relKeB'=>array(selff::HAS_MANY, 'B', 'A_ID'),
		);
	}

kita dapat menggunakan attribut with pada CDbCriteria

$criteria= new CDbCriteria;
$criteria->select= '`id`,sum(`price`) as Total,count(`B_id`) as jumlah, max(`price`) as maks,min(`price`) as minimum';
$criteria->with='relKeB';
A::model()->findAll($criteria);

semoga berhasil

#11 saebaryo

Junior Member

Group: Members
Posts: 74
Joined: 13-October 10
Location:jogjakarta - Indonesia

Posted 06 January 2011 - 12:38 PM

makasih penjelesannya gan, semakin menambah pengetahuan.
cuma cara mengakses Total itu gimana ya ?

ane coba yang lebih sederhana spt ini :

  $criteria= new CDbCriteria;
  $criteria->select= 'sum(`nominal`) as Total';
  $cari=A::model()->findAll($criteria);
  $jumlahTotal = $cari->Total;

  return $jumlahTotal;

nah dibrowser tampil pesan spt ini :

CException

Deskripsi
Properti "A.Total" tidak didefinisikan.

karena waktu mendesak selama ini sih ane pake algoritma penjumlahan aja gan, cm yg ky gitu kan gak keren

everything must standing on balance

#12 nasrul

Junior Member

Group: Members
Posts: 98
Joined: 14-April 09
Location:Sorowako - Indonesia

Posted 07 January 2011 - 12:04 AM

saebaryo, on 06 January 2011 - 12:38 PM, said:

makasih penjelesannya gan, semakin menambah pengetahuan.
cuma cara mengakses Total itu gimana ya ?

ane coba yang lebih sederhana spt ini :

  $criteria= new CDbCriteria;
  $criteria->select= 'sum(`nominal`) as Total';
  $cari=A::model()->findAll($criteria);
  $jumlahTotal = $cari->Total;

  return $jumlahTotal;

coba pakai

$cari['Total'];

kalau mau pakai $cari->total, sepertinya harus definisikan

public $_total;

dari class model A.

#13 saebaryo

Junior Member

Group: Members
Posts: 74
Joined: 13-October 10
Location:jogjakarta - Indonesia

Posted 08 January 2011 - 11:01 AM

nasrul, on 07 January 2011 - 12:04 AM, said:

coba pakai

$cari['Total'];

kalau mau pakai $cari->total, sepertinya harus definisikan

public $_total;

dari class model A.

Sukses gan, Makasih banget ya agan nasrul, tp mungkin bisa dijelaskan napa mesti di definisikan

public $_total;

terlebih dahulu ?
kalo ada link referensinya, ntar ane baca sendiri, soalnya yg pernah ane baca gak ada keterangan spt itu ato mungkin ada yg terlewatkan yah

sebelumnya ane udah coba pake setAttribut, dll tp gagal mulu.

o iya punya ane gak ada underscore( _ ) nya. soalnya tetap errornya kek yg sebelumnya kalo di kasih underscore ( _ ) karena "as" nya "total" gitu aja gak pake _

everything must standing on balance

#14 nasrul

Junior Member

Group: Members
Posts: 98
Joined: 14-April 09
Location:Sorowako - Indonesia

Posted 09 January 2011 - 04:35 AM

saebaryo, on 08 January 2011 - 11:01 AM, said:

Sukses gan, Makasih banget ya agan nasrul, tp mungkin bisa dijelaskan napa mesti di definisikan

public $_total;

terlebih dahulu ?
kalo ada link referensinya, ntar ane baca sendiri, soalnya yg pernah ane baca gak ada keterangan spt itu ato mungkin ada yg terlewatkan yah

alhamdulillah, ana cuma ingat2 tutorial blog pada bagian mendefinisikan model User. http://www.yiiframew.../prototype.auth
secara pada model A memang tidak ada atribut 'total', sehingga $A->total tidaklah dikenal. untuk itu kita perlu mendefinisikan atributnya secara public agar dapat diakses dari mana saja.
semoga cukup memberikan gambaran, dan mohon maaf atas keterbatasan ana.

#15 saebaryo

Junior Member

Group: Members
Posts: 74
Joined: 13-October 10
Location:jogjakarta - Indonesia

Posted 09 January 2011 - 11:54 AM

mantap penghayatan agan :lol:

everything must standing on balance

#16 edoriansyah

Newbie

Group: Members
Posts: 6
Joined: 25-March 11
Location:Depok, Indonesia

Posted 20 June 2011 - 11:40 PM

Mantap gan.. Tapi ane belum tau cara menampilkan hasilnya ke CGridView?

Mohon pencerahannya dr agan2 sekalian.

Yii Framework Forum: sum, count, max, min bersamaan - Yii Framework Forum

sum, count, max, min bersamaan tentang group by, agregasi, STAT Rate Topic: 1 Votes

#1 aqge

#2 nasrul

#3 junxiong

#4 aqge

#5 nasrul

#6 aqge

#7 YII alvs

#8 aqge

#9 saebaryo

#10 nasrul

#11 saebaryo

#12 nasrul

#13 saebaryo

#14 nasrul

#15 saebaryo

#16 edoriansyah

Share this topic:

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users

Delete Post

Skin and Language

Execution Stats

Yii Framework Forum: sum, count, max, min bersamaan - Yii Framework Forum

Share this topic:

1 User(s) are reading this topic 0 members, 1 guests, 0 anonymous users

Delete Post

Skin and Language

Execution Stats

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users