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,machine_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)