By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

get day records based on id

P: 8
hi,

am using query as
Expand|Select|Wrap|Line Numbers
  1. 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,

Expand|Select|Wrap|Line Numbers
  1. string rdate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
Expand|Select|Wrap|Line Numbers
  1. MachineID Name Quantity BilledAmount ReceivedAmount
  2. V0001 petrol 61 244 244
  3. V0002 Diesel 186 726 726
  4. V0002 petrol 155 605 605
  5. Total 402 1575 1575
But i want for every machine id ,total row

Expand|Select|Wrap|Line Numbers
  1. V0001 - - - -
  2. total - - -
  3. V0001 - - - -
  4. total - - -
Mar 14 '14 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,031
"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'
Mar 14 '14 #2

P: 8
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
Mar 14 '14 #3

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

V0001 Petrol - - -
Diesel - - -
Total - - -
V0002 Petrol - - -
Diesel - - -
total - - -
Mar 14 '14 #4

Expert 100+
P: 1,031
Expand|Select|Wrap|Line Numbers
  1. select 
  2.     MachineID, 
  3.     Name, 
  4.     Quantity, 
  5.     BilledAmount, 
  6.     ReceivedAmount 
  7. from (
  8.     Select 1, 
  9.         machine_id as MachineID,
  10.         item_name as Name,
  11.         SUM(item_quantity) as Quantity,
  12.         SUM(billed_amount) as BilledAmount,
  13.         SUM(received_amount) as ReceivedAmount 
  14.     from transaction 
  15.     where DATE(paid_date)='" + rdate + "' 
  16.     Group by machine_id,item_name
  17. union 
  18.     Select 2, 
  19.         machine_id as MachineID,
  20.         '' as Name,SUM(item_quantity) as Quantity,
  21.         SUM(billed_amount) as BilledAmount,
  22.         SUM(received_amount) as ReceivedAmount 
  23.     from transaction 
  24.     where DATE(paid_date)='" + rdate + "' 
  25.     Group by machine_id
  26. order by MachineID, 1)
  27.  
Mar 14 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.