get day records based on id

[rajeswari02]

hi,

am using query as

Select machine_id as MachineID,item_name as Name,SUM(item_quantity) as Quantity,SUM(billed_amount) as BilledAmount,SUM(received_amount) as ReceivedAmount from transaction where DATE(paid_date)='" + rdate + "'Group by machine_id,item_name

here rdate , am collecting from UI,

string rdate = dateTimePicker1.Value.ToString("yyyy-MM-dd");

MachineID Name Quantity BilledAmount ReceivedAmount
V0001 petrol 61 244 244
V0002 Diesel 186 726 726
V0002 petrol 155 605 605
Total 402 1575 1575

But i want for every machine id ,total row

V0001 - - - -
total - - -
V0001 - - - -
total - - -

[Luuk]

"But i want for every machine id ,total row"

than you should GROUP, and SUM() things in your query, not on 'machine_id' and 'item_name' but only on 'machine_id'

[rajeswari02]

Based on item_name also actually
i am having records with name"petrol" more than 1 per day , so i need to sum() based on item_name,machi ne_id

[rajeswari02]

above out put is after the querying database.
i want output as

V0001 Petrol - - -
Diesel - - -
Total - - -
V0002 Petrol - - -
Diesel - - -
total - - -

[Luuk]

select 
	MachineID, 
	Name, 
	Quantity, 
	BilledAmount, 
	ReceivedAmount 
from (
	Select 1, 
		machine_id as MachineID,
		item_name as Name,
		SUM(item_quantity) as Quantity,
		SUM(billed_amount) as BilledAmount,
		SUM(received_amount) as ReceivedAmount 
	from transaction 
	where DATE(paid_date)='" + rdate + "' 
	Group by machine_id,item_name
union 
	Select 2, 
		machine_id as MachineID,
		'' as Name,SUM(item_quantity) as Quantity,
		SUM(billed_amount) as BilledAmount,
		SUM(received_amount) as ReceivedAmount 
	from transaction 
	where DATE(paid_date)='" + rdate + "' 
	Group by machine_id
order by MachineID, 1)