get day wise records seperated by id
-
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 - - -
-
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 - - -
You are trying to make the database do the work of the UI, wrong tool for the job. You either need to use a reporting tool that will format the totals or do it yourself. This depends on your consumer. Good to see you asking a more sensible question. Do some research into parameterised queries and "Little Bobby Tables". One will make you life easier and the other may save you from disaster.
Never underestimate the power of human stupidity RAH
-
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 - - -
Your GUI is written in C#, isn't it? So I'd suggest to do that extra calculation and formatting there.