Need all row IDs even though they're grouped!

[nzmike99]

Hi all,

Given the query below...

select Sum(Price), Max(PurchaseCurrency)
from ShoppingCartItems
where PurchaseCurrency is not null
group by PurchaseCurrency

...how could I also get a text field containing all the ItemIDs (the ID column of ShoppingCartItems) for the grouped rows? I'd like to get an extra field back with something like this:
"10245;10246;10335;106678;...etc" which are all the ItemID values in that currency group.

I thought I could use collate in some form but have had no luck so far. This *must* be simple so if anyone can point me gently in the right direction I'd appreciate it greatly.

TIA...

Mike

 

[jsteph]

What you are looking for is an Aggregate function, such as Sum(), Avg(), but called Concat(). This does not currently exist in any sql database I know of.

It would be helpful, but you can imagine the logistical implications of such a function.
--Jim

 

[jsteph]

...you could, however, code your own function. The logistical implications still exist--if a group has 1,000,000 rows, you may not be able to return that long a string in a function.
--Jim

 

[nzmike99]

Well, I did have another idea - instead of getting all the ItemID's that make up the group, I thought of assigning a single GUID (or ID column) to each row in each group, then later I can use my grouped data along with the unique GUID to identify the individual ShoppingCartItems I need.

For example my source rows might look like this:

Currency Price GUID
AUD 1.11 C6BB7D5FE56BDC66A
AUD 2.22 C6BB7D5FE56BDC66A
AUD 3.33 C6BB7D5FE56BDC66A
USD 1.50 7D73BCDA7B48B9E9F8
USD 2.25 7D73BCDA7B48B9E9F8
USD 0.75 7D73BCDA7B48B9E9F8

and my grouped data would lthen look this:

AUD 6.66 C6BB7D5FE56BDC66A
USD 4.50 7D73BCDA7B48B9E9F8

Any ideas on how I might do that? I think it would also be much more efficient than trying to collect all the ItemId's. It also means I don't need to parse all the item ID's in my C# app once I get the data back.

I'm actually beginning to think I might need to use a (gasp!) cursor here to step through the ungrouped data to insert the unique GUID or ID column for each group. i was hoping to find a straight SQL solution but my hopes are fading!

Thanks again,

Mike

 

[r937]

What you are looking for is an Aggregate function, such as Sum(), Avg(), but called Concat(). This does not currently exist in any sql database I know of.

in mysql, GROUP_CONCAT

in sybase asa, LIST



r937.com | rudy.ca

 

[nzmike99]

Damn, what a shame none of those exist in SQL Server!

 

[jsteph]

r937,
Thank you for that info, that could be a very useful aggregate function. Do you know what it's limitations are, ie, max length or max number of group items?
--Jim

 

[r937]

sorry, no, i don't know

maybe check the docs?

r937.com | rudy.ca

 

[vongrunt]

in mysql, GROUP_CONCAT

in sybase asa, LIST

in clipper, DBEVAL() .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

THAT'S IT! I'm using Clipper from now on. Anyone know where I can buy a copy?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Nevermind. I found it.

http://www.grafxsoft.com/cgi-shl/grx_store.exe

It's only $200. That's a fraction of the cost for SQL Server, and it includes a front end, too.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BoxHead]

Here's a free one for access.

Public Function ConcatField(MyFld As String, MyBreak As String, _
TblQryName As String, Optional MyCrt As String) As String

Dim myString As String, strSQL As String
Dim db As Database, rst As Recordset

If MyCrt = "" Then
strSQL = "SELECT" & MyFld & " FROM " & TblQryName & ";"
Else
strSQL = "SELECT " & MyFld & " FROM " & TblQryName & " WHERE " & MyCrt & ";"
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rst.EOF
If InStr(myString, rst.Fields(0) & MyBreak) = 0 Then
myString = myString & rst.Fields(0) & MyBreak
End If
rst.MoveNext
Loop

If Len(myString) > Len(MyBreak) Then
ConcatField = Left(myString, Len(myString) - Len(MyBreak))
Else
ConcatField = myString
End If

End Function

Call it like a DMax but include a separator character (see the comma in bold):

ConcatField("[myField],", ","myTableQuery", "myCriteria")

This should work, Mike:

ConcatField("[ItemId]",", ","ShoppingCartItems","Not isnull([PurchaseCurrency])")

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[nzmike99]

Thanks John, but as I pointed out in on eof my other posts the actual query I need to do this form is more complicated and uses a group-by which means I can't use ItemId as it's not in an aggregate.

I've finally resigned myself to doing this whole exercise in C# with a recordset of the correct rows I need... so it will be similiar to what you've got there but more more complex. There's just no simple way I can do in SQL Server so I'll have to do with code unfortunately.

Thanks for the input though, it's all appreciated!

Mike

 

[vongrunt]

Funny thing, every two or three days a new post appears with basically the same problem.

Make two queries, one with SUM() stuff grouped by PurchaseCurrency, another one returning semicolon-separated list of ItemIDs for each PurchaseCurrency (check thread183-1129457 for some ideas). Join both queries (derived tables) and voila.

Yes, this is sometimes easier to do client-side... that way you don't even have to SUM() things server-side. Simply pass flat SELECT query and group rows client-side with trivial nested loop algorithm.

And yes, I'd like to have CONCAT() too.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[nzmike99]

Anyone know if there's anything like in SQL Server 2005?

 

[BoxHead]

Missed the group by. I think the citeria line can handle that. Again, I'm using this in access. It will add the ConcatField to the Group By but the ItemIds will be the group of IDs that have that PurchaseCurrency.

ConcatField("[ItemId]",", ","ShoppingCartItems","Not isnull([PurchaseCurrency]) [b]AND [PurchaseCurrency] = """" & [PurchaseCurrency] & """")[/b]

I tried to mirror your query on an Orders table and the results were fine:

SELECT Sum(oPartTotalQty), Max(oPayTerms), concatfield("[OrderId]",", ","tblOrder","not isnull([oPayTerms]) AND [oPayTerms] = " & """" & [oPayTerms] & """")
FROM tblOrder
GROUP BY concatfield("[OrderId]",", ","tblOrder","not isnull([oPayTerms]) AND [oPayTerms] = " & """" & [oPayTerms] & """"), tblOrder.oPayTerms;



HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[nzmike99]

Hey, thanks John, I'll give that a try on Monday morning when I have to face this horrid problem again! I did notice you mentioned Access in your first post - did you get this working in SQL Server as well?

Cheers,

Mike