Group By????

[Stephen McCormack]

Hi,

I have a pretty simple problem for a SQL programmer regarding GROUPINGS:

I have two tables tblShoppingCarts and tblProducts.
The first table stores all shopping cart data.
I would like to retrieve all this data (cart and all related product
information) BUT need to GROUP BY tblShoppingCarts.sessionid.

Also, any suggestions on fields to include in the shopping cart would be
much appreciated.

Thanks for your help.

NOTE: To retreive all cart information run the stored procedure EXEC
spGetShoppingCarts NULL, NULL

--------------------------------------------

CREATE TABLE [dbo].[tblProducts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[category] [int] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[title] [varchar] (100) NOT NULL ,
[shorttitle] [varchar] (30) NULL ,
[version] [float] NOT NULL ,
[build] [int] NOT NULL ,
[shortDescription] [varchar] (1000) NULL ,
[description] [varchar] (4000) NOT NULL ,
[retailPrice] [money] NOT NULL ,
[oemPrice] [money] NULL ,
[hasImage] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblShoppingCarts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[sessionid] [bigint] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[modifieddate] [smalldatetime] NULL ,
[productid] [int] NOT NULL ,
[quantity] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE spGetShoppingCarts

@id int = NULL,
@sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@id IS NULL) AND (@sessionid IS NULL)

BEGIN

SELECT
sc.[id],
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.sessionid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.[id],
---I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
WORK??????
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity

ORDER BY
sc.[modifieddate] DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@id IS NULL)
BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
WHERE sc.sessionid = @sessionid )
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.productid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity
END
END
GO

[Stephen McCormack]

Thanks everyone for your help!

I finally worked out after a bit of reading exactly how the GROUP BY
statement works and why I would only use it if I where aggregating
information (ie SUM, COUNT etc).
The solution for the stored procedure was:
CREATE PROCEDURE spGetShoppingCarts

@id int = NULL,
@sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@id IS NULL) AND (@sessionid IS NULL)

BEGIN

SELECT
sc.sessionid,
LastUpdated = MAX( sc.publishdate ),
TotalPrice = SUM(sc.quantity * p.retailPrice)
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.sessionid
ORDER BY
2 DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@id IS NULL)
BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity,
sc.discount,
sc.code ,
p.retailPrice
END
END
GO


"Stephen McCormack" <st******@mwebsolutions.com.au> wrote in message
news:Xd****************@news-server.bigpond.net.au...

Hi,

I have a pretty simple problem for a SQL programmer regarding GROUPINGS:

I have two tables tblShoppingCarts and tblProducts.
The first table stores all shopping cart data.
I would like to retrieve all this data (cart and all related product
information) BUT need to GROUP BY tblShoppingCarts.sessionid.

Also, any suggestions on fields to include in the shopping cart would be
much appreciated.

Thanks for your help.

NOTE: To retreive all cart information run the stored procedure EXEC
spGetShoppingCarts NULL, NULL

--------------------------------------------

CREATE TABLE [dbo].[tblProducts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[category] [int] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[title] [varchar] (100) NOT NULL ,
[shorttitle] [varchar] (30) NULL ,
[version] [float] NOT NULL ,
[build] [int] NOT NULL ,
[shortDescription] [varchar] (1000) NULL ,
[description] [varchar] (4000) NOT NULL ,
[retailPrice] [money] NOT NULL ,
[oemPrice] [money] NULL ,
[hasImage] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblShoppingCarts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[sessionid] [bigint] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[modifieddate] [smalldatetime] NULL ,
[productid] [int] NOT NULL ,
[quantity] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE spGetShoppingCarts

@id int = NULL,
@sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@id IS NULL) AND (@sessionid IS NULL)

BEGIN

SELECT
sc.[id],
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.sessionid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.[id],
---I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
WORK??????
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity

ORDER BY
sc.[modifieddate] DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@id IS NULL)
BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
WHERE sc.sessionid = @sessionid )
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.productid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity
END
END
GO