October 19, 2007 at 3:50 pm
Here are the two statements and the relative results.
select * from details where committed_fund_id = 26515
SELECT DISTINCT INV_ACCT_ID,LINE_ITEM_NAME,ACTIVITY_ID,COMMITTED_FUND_ID,SUPPLIER,OWNER,INVOICE,SUM(committed_fund) AS COMMITTED_FUND
FROM details
where committed_fund_id = 26515
GROUP BY INV_ACCT_ID,LINE_ITEM_NAME,ACTIVITY_ID,COMMITTED_FUND_ID,SUPPLIER,OWNER,INVOICE
First result
inv_acct_id activity_id committed_fund_item_id COMMITTED_FUND_ID SUPPLIER OWNER committed_fund invoice line_item_name line_item_id
2004 35101 25815 26515 WINTHROP PRINTING CO INC Renee Conklin 17708.52 144913.32 Merchandising - Brand 223
2004 35101 26841 26515 WINTHROP PRINTING CO INC Renee Conklin 9221.74 144913.32 Merchandising - Brand 223
2004 35101 26842 26515 WINTHROP PRINTING CO INC Renee Conklin 5648.18 144913.32 Merchandising - Brand 223
2004 35101 26843 26515 WINTHROP PRINTING CO INC Renee Conklin 13328.83 144913.32 Merchandising - Brand 223
2004 35101 26844 26515 WINTHROP PRINTING CO INC Renee Conklin 3321.3 144913.32 Merchandising - Brand 223
2004 35101 26845 26515 WINTHROP PRINTING CO INC Renee Conklin 3725.33 144913.32 Merchandising - Brand 223
2004 35101 26846 26515 WINTHROP PRINTING CO INC Renee Conklin 3769.83 144913.32 Merchandising - Brand 223
2004 35101 26847 26515 WINTHROP PRINTING CO INC Renee Conklin 30250.43 144913.32 Merchandising - Brand 223
2004 35101 26848 26515 WINTHROP PRINTING CO INC Renee Conklin 46788.28 144913.32 Merchandising - Brand 223
2004 35101 26849 26515 WINTHROP PRINTING CO INC Renee Conklin 11150.88 144913.32 Merchandising - Brand 223
Second Result:
INV_ACCT_ID LINE_ITEM_NAME ACTIVITY_ID COMMITTED_FUND_ID SUPPLIER OWNER INVOICE COMMITTED_FUND
2004 Merchandising - Brand 35101 26515 WINTHROP PRINTING CO INC Renee Conklin 144913.32 70661.29
2004 Merchandising - Brand 35101 26515 WINTHROP PRINTING CO INC Renee Conklin 144913.32 74252.03
I am using SQL 2005 and really stuck. This is only Committed Fund (aka PO) that will not sum and group correctly.
Thanks, for the help,
Bill
October 19, 2007 at 4:41 pm
Your getting each individual record because you have activity_id in your select. Remove it and you'll get the totals.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 19, 2007 at 7:06 pm
The activity_id is the same for all detail rows (and besides I need it).
This grouping work for all other detail records - all but this Committed fund. @!$@%!##
Thanks for looking at it,
Bill
October 19, 2007 at 7:35 pm
I think you'll find the problem in one of the text columns... you may have some trailing spaces in some of the descriptions. You can check for those by comparing the DATALENGTH against the LEN of each column... if they are different for any given row, you will have identified the rows and items that are the culprit.
--Jeff Moden
October 19, 2007 at 8:34 pm
Not objecting to Jeff's comment, and I was off on reading the columns so I meant to say the "committed_fund_item_id" column but ...
Bill Maners (10/19/2007)
The activity_id is the same for all detail rows (and besides I need it).This grouping work for all other detail records - all but this Committed fund. @!$@%!##
Thanks for looking at it,
Bill
and ...
inv_acct_id activity_id committed_fund_item_id COMMITTED_FUND_ID SUPPLIER OWNER committed_fund invoice line_item_name line_item_id
2004 35101 25815 26515 WINTHROP PRINTING CO INC Renee Conklin 17708.52 144913.32 Merchandising - Brand 223
2004 35101 26841 26515 WINTHROP PRINTING CO INC Renee Conklin 9221.74 144913.32 Merchandising - Brand 223
2004 35101 26842 26515 WINTHROP PRINTING CO INC Renee Conklin 5648.18 144913.32 Merchandising - Brand 223
Leads me to believe that the "committed_fund_item_id" column is the source of your problem. Honestly, I'm outta here for quite a while so I can't work on a solution for you right now. Hopefully this info helps. Do you really need each "committed_fund_item_id" in the results.
Maybe Jeff has the time to give the answer. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 20, 2007 at 8:35 am
Heh... I thought the same thing... but there's two columns that look very much alike...
Committed_Fund_ID
Committed_Fund_Item_ID
I don't see the Committed_Fund_Item_ID being used in the problem GROUP BY query... 'course, the formatting is pretty bad and so are my eyes so I might be mistaken...
On the lighter side... Jason, I love your avatar... You related to a fellow by the name of "Kilroy" by any chance? 😀
--Jeff Moden
October 20, 2007 at 11:08 am
Jeff Moden (10/20/2007)
Heh... I thought the same thing... but there's two columns that look very much alike...Committed_Fund_ID
Committed_Fund_Item_ID
I don't see the Committed_Fund_Item_ID being used in the problem GROUP BY query... 'course, the formatting is pretty bad and so are my eyes so I might be mistaken...
On the lighter side... Jason, I love your avatar... You related to a fellow by the name of "Kilroy" by any chance? 😀
Man, my eyes are going bad too! Now correct me if I'm wrong, but wouldn't the statement fail without having the Committed_Fund_Item_ID in the GROUP BY? :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 20, 2007 at 12:29 pm
Jason Selburg (10/20/2007)
Man, my eyes are going bad too! Now correct me if I'm wrong, but wouldn't the statement fail without having the Committed_Fund_Item_ID in the GROUP BY? :hehe:
Not in this particular case, as it's not in the SELECT statement. Yep, I had to look at the code several times to be sure as well. I'm a big fan of a line per column in code. 🙂
What's the datatype of Invoice, by the way? If that's not it, then I'd recommend you simplify the query where it's just a single column with the sum, adding a column at a time until you find the culprit (the first one that returns two rows). At that point, you'll know which column it is, and assuming it's character data, a varbinary will show what the difference is between the two values. Could be something as simple as a leading space, zero instead of letter "O", etc.
October 20, 2007 at 4:00 pm
David McFarland (10/20/2007)
Jason Selburg (10/20/2007)
Man, my eyes are going bad too! Now correct me if I'm wrong, but wouldn't the statement fail without having the Committed_Fund_Item_ID in the GROUP BY? :hehe:Not in this particular case, as it's not in the SELECT statement. Yep, I had to look at the code several times to be sure as well. I'm a big fan of a line per column in code. 🙂
ARGH, I'll go and bury my head now... *laughs*
I just re-read through the original post and it's all to clear now. I'd go with David's approach.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 22, 2007 at 7:52 am
The invoice column is FLOAT (Not NULL).
And it is the culprit.
When I group by INVOICE, I two rows. The following query:
SELECT DISTINCT
--INV_ACCT_ID
--,LINE_ITEM_NAME
--,ACTIVITY_ID
--,COMMITTED_FUND_ID
--,SUPPLIER
--,OWNER
--,
INVOICE
--,
--SUM(committed_fund) AS COMMITTED_FUND
FROM details
where committed_fund_id = 26515
GROUP BY
--INV_ACCT_ID
--,LINE_ITEM_NAME
--,ACTIVITY_ID
--,COMMITTED_FUND_ID
--,SUPPLIER
--,OWNER
--,
INVOICE
Returns the following result:
144913.32
144913.32
In addition, I get same result (two rows) when I ask for only the distinct values of INVOICES ???
SELECT DISTINCT
INVOICE
FROM details
where committed_fund_id = 26515
Here is the table defintion:
sp_columns details
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
CB_Production dbo details inv_acct_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56
CB_Production dbo details activity_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 2 NO 56
CB_Production dbo details committed_fund_item_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 3 NO 56
CB_Production dbo details COMMITTED_FUND_ID 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 4 YES 38
CB_Production dbo details SUPPLIER -9 nvarchar 75 150 NULL NULL 1 NULL NULL -9 NULL 150 5 YES 39
CB_Production dbo details OWNER -9 nvarchar 106 212 NULL NULL 1 NULL NULL -9 NULL 212 6 YES 39
CB_Production dbo details committed_fund 6 float 15 8 NULL 10 0 NULL NULL 6 NULL NULL 7 NO 62
CB_Production dbo details invoice 6 float 15 8 NULL 10 0 NULL NULL 6 NULL NULL 8 NO 62
CB_Production dbo details line_item_name -9 nvarchar 75 150 NULL NULL 0 NULL NULL -9 NULL 150 9 NO 39
CB_Production dbo details line_item_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 10 NO 56
Thanks for the help !!
Bill
October 22, 2007 at 8:01 am
Floats are not an exact field... Eg, 1.1 cannot be represented in IEEE floating point arithmetic on computers. You should change your invoice field (if it really needs decimal points) to be a DECIMAL/NUMERIC (same thing) field rather than a float field. These fields are exact in their value. Chances are, you're seeing 144913.32 when displaying the value but really one is 144913.3200001 and the other is 144913.32000032 or something. You could convert both to ints but then you're losing the benefit of any index you have in place and are still not really solving the underlying problem.
October 22, 2007 at 10:59 am
I used the round function on the INVOICE column for the group and all is well.
Thanks for the support,
Bill
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply