Need all row IDs even though they're grouped!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need all row IDs even though they're grouped!

Author

Topic

nzmike
Starting Member

21 Posts

Posted - 2005-09-28 : 00:57:06

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 01:00:08

You should use two queries and join them

For concatenating ids refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

nzmike
Starting Member

21 Posts

Posted - 2005-09-28 : 04:51:35

quote:
Originally posted by madhivanan

You should use two queries and join them

Thanks very much for the input - it's really appreciated.

I looked at that article and did what I think is the first part ok (inserting the result of the grouped query into a new table) but the 2nd part (to create a query that will loop through ShoppingCartItem table to concatenate ID Column by PurchaseCurrency and insert the result to another table) I just can't seem to get working. The problem is really that my query is much more complicated than what I posted above (that was just to illustrate what I wanted to do at a very basic level) and I can't seem to get the 2nd part to work. Here's what I have so far:

** PART ONE - WORKS FINE **

CREATE TABLE #CartItemsGrouped
(
Ident INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PurchaseCurrency CHAR(3) NOT NULL,
ArtistID INT NOT NULL,
IsPublished INT NOT NULL,
MPGrossAmount DECIMAL(9,2) NOT NULL,
TrackCount INT NOT NULL,
ArtistEmail NVARCHAR(50) NOT NULL,
APRARef NVARCHAR(50),
ItemIDs VARCHAR(1000) NOT NULL
)

DECLARE @iStartDate datetime
DECLARE @iFinishDate datetime
DECLARE @iCurrencyCode varchar(3)
SELECT @iStartDate = '2005-09-21 00:00:00.000'
SELECT @iFinishDate = '2005-09-28 23:59:59.000'
SELECT @iCurrencyCode = 'AUD'

INSERT #CartItemsGrouped
(
PurchaseCurrency,
ArtistID,
IsPublished,
MPGrossAmount,
TrackCount,
ArtistEmail,
APRARef,
ItemIDs
)
SELECT
MAX(ci.purchasecurrency) PurchaseCurrency,
MAX(ci.artistid) ArtistID,
MAX(w.ispublished) ispublished,
SUM(ci.purchaseprice) MPGrossAmount,
COUNT(*) as TrackCount,
MAX(ad.A_Email) ArtistEmail,
MAX(a.Reference) APRARef,
'' as ItemIDs
FROM
ShoppingCartItems ci,
PayPalTransactions pp,
Works w,
ArtistsDetails ad,
Artists a
WHERE pp.cartid = ci.cartid
AND pp.PaymentResult = 'COMPLETED'
AND pp.PaymentDate >= @iStartDate
AND pp.PaymentDate < @iFinishDate
AND ci.PurchaseCurrency = @iCurrencyCode
AND ci.ArtistID = ad.ArtistID
AND ci.PurchasePrice > 0
AND ad.ArtistID = a.ArtistID
AND ci.workid = w.workid
GROUP BY ci.purchasecurrency, ci.artistid, a.Reference, w.ispublished

For the test dates and currency ($AUD) I'm using that gives me 7 grouped rows (representing 35 input rows) with all the data I need grouped perfectly plus an empty "ItemIDs" column. For each row I now want to get the ci.ItemID's that make up that group but this is where I get stuck. I take it I need to repeat something very similiar to the above to end up with another table of 7 rows with just "Ident" and the concatenated ItemIDs field but everything I've tried has failed so if you could give me a rough idea of what I need to do I'd be VERY grateful!

(I know you don't know our database or data but what we're trying to do is get a batch of sales items from the ShoppingCartItems table, grouped by shopping cart currency, artist ID (and the other two fields) into groups to pass, as a batch, to the PayPal MassPay API so we can automatically pay Artists for sales they get of music they sell on our site. I need to know the individual ItemIDs that make up each group so once the MassPay batch has run OK I can update each ShoppingCartItem with the reference returned from MassPay for that particular batch... this way I know whether the artist has already been paid for that item next time I run our automatic payments service runs.)

Hope I haven't confused you even more!! Many thanks again if you (or anyone) can spare a few moments to give me an idea of how to do this... I have to admit my SQL Server skills are a bit basic at times!

Mike

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-28 : 05:06:04

not sure how you did it but...

1. create a function that will return the IDs you need based on a criteria passed or parameter like PurchaseCurrency

create function fnItemIDs(@purchasecurrency varchar(10)) --forgot the data type, just supply what's appropriate from your table
returns varchar(4000)
as
begin
declare @strID varchar(4000)
select @strID=coalesce(@strID + ',','') + itemID from shoppingCartItems
where purchasecurrency=@purchasecurrency

return @strID
end

2. then using your group by you can query...

select TotPrice,Currency,dbo.fnItemIDs(currency) from
(
select Sum(Price) as 'TotPrice', Max(PurchaseCurrency) as 'Currency'
from ShoppingCartItems
where PurchaseCurrency is not null
group by PurchaseCurrency
) s

haven't tested this, but i hope you get the general idea

HTH

--------------------
keeping it simple...

nzmike
Starting Member

21 Posts

Posted - 2005-09-28 : 19:44:54

quote:
Originally posted by jen

not sure how you did it but...

1. create a function that will return the IDs you need based on a criteria passed or parameter like PurchaseCurrency

create function fnItemIDs(@purchasecurrency varchar(10)) --forgot the data type, just supply what's appropriate from your table
returns varchar(4000)
as
begin
declare @strID varchar(4000)
select @strID=coalesce(@strID + ',','') + itemID from shoppingCartItems
where purchasecurrency=@purchasecurrency

return @strID
end

2. then using your group by you can query...

select TotPrice,Currency,dbo.fnItemIDs(currency) from
(
select Sum(Price) as 'TotPrice', Max(PurchaseCurrency) as 'Currency'
from ShoppingCartItems
where PurchaseCurrency is not null
group by PurchaseCurrency
) s

Thanks Jen, I like the idea but have not had too much luck getting it going. As you can see from my previous post my actual query is a bit more complicated than the initial one I posted... here's what O have so far but it just returns me one single row with one single item ID - not sure why!

create function fnItemIDs(
@purchasecurrency varchar(3),
@StartDate DateTime,
@FinishDate Datetime
)
returns varchar(1000)
as
begin
declare @strID varchar(1000)
select @strID=coalesce(@strID + ',','') + ci.itemID
--from shoppingCartItems
--where purchasecurrency=@purchasecurrency
FROM
ShoppingCartItems ci,
PayPalTransactions pp,
Works w,
ArtistsDetails ad,
Artists a
WHERE
pp.cartid = ci.cartid
AND pp.PaymentResult = 'COMPLETED'
AND pp.PaymentDate >= @StartDate
AND pp.PaymentDate < @FinishDate
AND ci.PurchaseCurrency = @purchasecurrency
AND ci.ArtistID = ad.ArtistID
AND ci.PurchasePrice > 0
AND ad.ArtistID = a.ArtistID
AND ci.workid = w.workid
ORDER BY ci.purchasecurrency, ci.artistid, a.Reference, w.ispublished

return @strID
end

If I change the ORDER BY to GROUP BY I get the error telling me ci.itemid is not contained in an aggregate function... but I can't aggregate it with MAX or MIN since then I'd only get one ItemId per group... so I'm still stuck as to how get this to work - any more ideas? Can you see if I've screwed something up in my function?

Cheers, thanks again for any help you can offer - this one is way beyond my (somewhat limited) SQL Server knowledge!

Mike

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-28 : 22:19:51

you can check your select query first if it gives you the result your're expecting

can't help you in that area coz i don't know the ddl (structure) and don't have any sample data

but if you can post the info we need, we might be able to help you out

--------------------
keeping it simple...

simpson
Starting Member

11 Posts

Posted - 2005-09-28 : 22:56:06

I have already put all the info on the top. I am trying to see how many tables I need and the relationship in the beginning. and then the objects , queris and the rest.... Any idea how ?

nzmike
Starting Member

21 Posts

Posted - 2005-09-28 : 23:15:32

Simpson, I think you've posted in the wrong thread! If not, what is your post about? It certianly bears no relation to my SQL problem.

Mike

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-28 : 23:27:32

your post i believe...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55788

a case of mistaken post my dear mr. watson

quote:
Originally posted by simpson

I have already put all the info on the top. I am trying to see how many tables I need and the relationship in the beginning. and then the objects , queris and the rest.... Any idea how ?

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources