How to select data based on date and time

Question

0.00/5 (No votes)

See more:

Hi all

bill_no  datetime                   amount
1;      "2014-12-23 17:36:07";      382.02
1;      "2014-12-23 09:08:37";      1452.38
2;      "2014-12-23 18:18:26";      472.99
2;      "2014-12-23 09:56:44";      380.95
3;      "2014-12-23 10:00:07";      666.67
3;      "2014-12-23 18:42:43";      159.18
4;      "2014-12-23 10:12:54";      200.00
4;      "2014-12-23 18:43:25";      77.31
5;      "2014-12-23 10:15:21";      7857.14
5;      "2014-12-23 18:45:27";      409.31
6;      "2014-12-23 20:50:56";      318.36
6;      "2014-12-23 10:16:00";      761.90
7;      "2014-12-23 20:59:59";      109.16
7;      "2014-12-23 10:16:58";      1571.43

Here how to get the total of all the bill_no's depending on datetime.

now if i am trying sum(amount) it's taking only single values from the duplicate rows.

Please tell me how to do this..

Posted 1-Feb-15 23:22pm

Black_Rose

Add a Solution

Comments

DamithSL 2-Feb-15 5:32am

what the sql statement you tried?

Black_Rose 2-Feb-15 5:48am

select 'ASales Amt' as tran_type, sum(amount) as net_total from tbl_transaction_master Where to_char(bill_date,'yyyy-MM-dd')>='2014-12-23' and to_char(bill_date,'yyyy-MM-dd')<='2014-12-23'

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bhagyashree Chaudhari - Member 10473634 · Answer 1 · 2015-04-19T21:09:00

Solution 1

You should try this

SQL

SELECT bill_no, sum(amount) as net_total FROM tbl_transaction_master
WHERE bill_date >='2014-12-23 00:00:00' AND bill_date <='2014-12-23 23:59:59'
GROUP BY bill_no
ORDER BY bill_no

it will work for you :) .

Thank you.

Posted 19-Apr-15 21:09pm

Bhagyashree Chaudhari - Member 10473634

Updated 19-Apr-15 21:11pm

v2