Newbie Question

I am trying to sum some data in a table, and I can't figure it out. Below
is some sample data
Date,Customer,Order_Amt,Paid_Amt
2004-10-02 00:00:00,101,1418.82,-1400.00
2004-10-02 00:00:00,101,265.21,-200.00,
2004-10-02 00:00:00,101,648.74,-648.74
2004-10-02 00:00:00,102,95.95,-95.95
2004-10-02 00:00:00,102,457.42,-450.00
I trying to sum all of the orders for each customer from yesterday, without
showing each order, just the date, customer number, total orders, total
paid. I also want to add a 5th column stating order_amt-Paid_amt. Although
pathetic, this is the furthest I got :
select date, cust, order_amt, paid_amt from day_sales
where date > getdate()-2
group by date, cust, order_amt, paid_amt
I would these results :
date,cust,order_total,paid_total,amt_owe
d
2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
Any help would be greatly appreciated.
ThanksTry this:
select date, cust,
Count(*) OrderCount,
Sum(order_amt) TotalAmt,
Sum(paid_amt) TotalPaid,
Sum(order_amt-Paid_Amt) Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||Hi
Check out GROUP BY and SUM in books online.
Try (untested);
Select date, cust, SUM(order_amt), SUM(paid_amt), SUM(order_amt)
-SUM(paid_amt) AS Outstanding, COUNT(*) AS No_Orders from day_sales
where date > getdate()-2
group by date, cust
Your getdate()-2 may not give you the exact information required if you
have times with the orders to round to day one way is to use convert e.g.
Select CONVERT(CHAR(8),date,112) AS Date, cust, SUM(order_amt),
SUM(paid_amt), SUM(order_amt) -SUM(paid_amt) AS Outstanding, COUNT(*) AS
No_Orders from day_sales
where CONVERT(CHAR(8),date,112) >= CONVERT(CHAR(8),getdate()-2,112)
group by CONVERT(CHAR(8),date,112), cust
John
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||thanks for your help, that did the trick. I have one quick question. How
does sql know how to count the total orders per customer ? What if I wanted
a count of all of the orders that equal 1418.82 ? I don't see anything in
the script below that points the count command to the customer number.
Thanks again for your help, I really appreciate it !!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
> Try this:
> select date, cust,
> Count(*) OrderCount,
> Sum(order_amt) TotalAmt,
> Sum(paid_amt) TotalPaid,
> Sum(order_amt-Paid_Amt) Balance
> from day_sales
> where date > DateAdd(day, -2, getdate())
> group by date, cust
> "J Abrams" wrote:
>|||The group By statement says to SQL:
1) Collect all the records which match the criteria, and group them into one
groups, based on the values of the columns Cust, and Date, Then
2) Output ONE ROW for each of those GROUPS...
3) Any expression in the Select Clause, which has an aggregate function
(Sum, Count, Min, Max, etc.) IS then evaluated for ALL The records in each o
f
those constructed groups...
"J Abrams" wrote:

> thanks for your help, that did the trick. I have one quick question. How
> does sql know how to count the total orders per customer ? What if I want
ed
> a count of all of the orders that equal 1418.82 ? I don't see anything in
> the script below that points the count command to the customer number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
>
>|||Hi
If you wanted to restrict the whole query to add up certain rows i.e an
order_amt value of 1418.82 then you would need to add this to the where
clause.
i.e.
where date > DateAdd(day, -2, getdate())
and order_amt = 1418.82
You should notice that the OrderCount column is less than without the
additional clause (unless the customer only ever orders the one
amount!).
If you wanted to have an additional count that summed everything but
counted the number of times they ordered for an amout of 1418.82
select date, cust,
Count(*) AS OrderCount,
SUM(CASE WHEN order_amt = 1418.82 THEN 1 ELSE 0 END) AS
OrderedSpecificAmtCount,
Sum(order_amt) AS TotalAmt,
Sum(paid_amt) AS TotalPaid,
Sum(order_amt-Paid_Amt) AS Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
The rest is in books online. Please spend some time reading it as is a
very rich source of information.
John
J Abrams wrote:
> thanks for your help, that did the trick. I have one quick question.
How
> does sql know how to count the total orders per customer ? What if I
wanted
> a count of all of the orders that equal 1418.82 ? I don't see
anything in
> the script below that points the count command to the customer
number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
out.
yesterday,
total