How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttid
Look at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid|||Oh, I see. I have to use Having and it's after the Group. It works great.
Thank you very much and have a great day!
"Geoff N. Hiten" wrote:
> Look at the "Having" clause of the select statement. "Having" works on
> "Group By" much like the 'Where" works on "From". In your case, you would
> add something like:
> HAVING Sum(Amount) > 0
> after your Group By section.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> > Hi
> > I'm builing this string dynamically in C# program. I need to add one more
> > filtering condition of "Sum(Amount) >0" to the Select statement. I added
> > it
> > but I got an error message. Can someone help on how to do this in one
> > select
> > statment?
> >
> > Thanks, Alpha
> >
> > select exttid,sum(amount) as FeeBalance from tblLedger
> > where exttid in
> > (select distinct(exttid) from tblBilling b
> > where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> > and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> > and void = 0
> > group by exttid
>
>

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one selec
t
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttidsql

How could I assign rank.

I have the below query

select [FI NAME], round (sum(FIWORKING.GROSPFT), 0)as FIGROSS from from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]
order by sum (FIWORKING."GROSPFT") DESC

Which returns below

name1 65784
name2 32586
name3 37892

based on this, I would like to be able to set a rank (aka 1 , 2 , 3, etc)
for NAME1, NAME2, etc

I would like to store this in table GROSPFTRANK

That Table looks like

name1 1
name2 2

I haven't been able to figure out the SQL to do this.

Thanks for an help

ChrisYou can create a table with an identity column. Insert into that table using your query and the ranking will automatically be applied.|||There are several ways to skin this cat.

One method is to make the second field in your destination table an incrementing identity column, and then just insert your ordered data into it.

A second method would be to create a temporary table with an autoincrement column and load your data into it prior to storing it in your permanent table.

Or you could use this sql statement, which runs two totals and then counts the number of records in the second set which are less than the value in the first set:

Insert into GROSPFTRANK ([FI NAME], [RANK])
select [FIWORKINGOUTER].[FI NAME], count([FIGROSS].[FI NAME])
from
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGOUTER
inner join
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGSUB
on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)

Note that this assigns two [FI NAME] values the same rank if they have the same summary value. If you want unique ranks based on, say, the alphabetical order of [FI NAME], join the two subqueries with this ON statement:

on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)
or (FIWORKINGOUTER.FIGROSS = FIWORKINGSUB.FIGROSS
and [FIWORKINGOUTER].[FI NAME] < [FIWORKINGSUB].[FI NAME])

blindman

How Can I Use a SubQuery to Sum a Column "Quantity" where subqry.item=mainqry.it

More to the point, how do I get the results to show one row per Item? I thought if I got all the rows evaluate the same, I would be able to use DISTINCT to return one row per item.

Actually, I thought if I did a RIGHT JOIN that for each instance (record) of an item in my ItemInventory table (in which all rows are unique) with my PurchaseOrderLine table (in which there can be multiple instances of an item) that I would get one lie, with the quantities on PO's that are open and received summed. Instead, I get multiple lines that all appear to have correct data but that are duplicates.

Here is my current query

SELECT ItemInventory.Name AS [Product Number],
ItemInventory.SalesDesc AS Description,
ItemInventory.PrefVendorRefFullName AS Vendor,
ItemInventory.QuantityOnHand AS Available,
ItemInventory.ReorderPoint AS Minimum,
(ItemInventory.QuantityOnHand-ItemInventory.ReorderPoint) AS Under,

(SELECT SUM(PurchaseOrderLine.PurchaseOrderLineQuantity)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS [On Order],

(SELECT SUM
(PurchaseOrderLine.PurchaseOrderLineReceivedQuanti ty)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS Recvd

PurchaseOrderLine.TxnDate AS [PO Date],
ItemInventory.PurchaseCost AS Price

FROM PurchaseOrderLine RIGHT JOIN ItemInventory ON PurchaseOrderLine.PurchaseOrderLineItemRefListID=I temInventory.ListID;

Here are my results:

"Large Box","Large Packing Box",,455,100,355,,,2
"Medium Box","Medium cardboard packing box",,55,100,-45,,,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Small Box","Small cardboard packing box",,55,100,-45,,,1
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Will Excel Delete This Item",,,100,,,,,0

If I use DISTINCT, like I thought I could, MS ACCESS blows up on me. I don't know if it is my SQL or MS ACCESS (or if my data source has issues).

If I can verify my SQL is valid, then I'll know if I need to look elsewhere for a resolution to my problem.

TIA

Danielyou need to develop a query like this in two steps

the first step is to ensure that the join is working correctly, and the second step is to add GROUP BY and aggregate functions like SUM (i think your subqueries in the SELECT clause are wrong, but let's do that later after we fix the join)

for your first step, you need to understand that your RIGHT JOIN will return each ItemInventory with or without matching rows from PurchaseOrderLine

in other words, all products

and for any product that appears on any PurchaseOrderLine, you will get a row for each such occurrence, and that product will appear in the results multiple times

is that what you want? if not, we need to fix the join before you start thinking about SUMs|||I need to look at every item in the InventoryItem table.

For those items where there are purchase orders in the PurchaseOrderLine table, I need to sum the total number on all open purchase orders, minus the number already received against those open purchase orders (they receive partial fills on orders).

In the end, I need somethig that looks like this:

Name Description Vendor #Available Reorder_Point Max_On_Hand #Under Reorder_Point #On_Order #To_Reorder Cost_Per_Unit Cost_to_Reorder Total_Cost_to_Reorder

#Under = Max_On_Hand - #Avaialble (if #Available < Max_On_Hand)

We reorder an item if #Under < 0

#To_Reorder = #Under - #On_Order (we never want the total headed for the shelves to be > Max_On_Hand|||select II.Name AS [Product Number]
, II.SalesDesc AS Description
, II.PrefVendorRefFullName AS Vendor
, II.QuantityOnHand AS Available
, II.ReorderPoint AS Minimum
, (II.QuantityOnHand
-II.ReorderPoint) AS Under
, SUM(POL.PurchaseOrderLineQuantity)
AS [On Order]
, SUM(POL.PurchaseOrderLineReceivedQuantity)
AS Recvd
, MAX(POL.TxnDate) AS [PO Date]
, II.PurchaseCost AS Price
from ItemInventory as II
left outer
join PurchaseOrderLine as POL
ON POL.PurchaseOrderLineItemRefListID
= II.ListID
group
by II.Name
, II.SalesDesc
, II.PrefVendorRefFullName
, II.QuantityOnHand
, II.ReorderPoint
, II.PurchaseCost|||I will try this.

I may ask what makes it tick, but I think I get it ... I will play with it before I ask again tho.