Group By????

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Stephen McCormack
    #1

    Group By????

    Hi,

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

    I have two tables tblShoppingCart s 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 tblShoppingCart s.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
    spGetShoppingCa rts 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 ,
    [shortDescriptio n] [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].[tblShoppingCart s] (
    [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 spGetShoppingCa rts

    @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 ProductShortTit le,
    sc.quantity,
    COUNT(sc.sessio nid) AS ProductCount
    --p.discount,
    --p.quantity AS DiscountQuantit y,
    --p.code
    FROM
    tblShoppingCart s 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 ProductShortTit le,
    sc.quantity,
    ProductCount = ( SELECT COUNT(sc.produc tid) FROM tblShoppingCart s sc
    WHERE sc.sessionid = @sessionid )
    --p.discount,
    --p.quantity AS DiscountQuantit y,
    --p.code
    FROM
    tblShoppingCart s 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 ProductShortTit le,
    sc.quantity,
    COUNT(sc.produc tid) AS ProductCount
    --p.discount,
    --p.quantity AS DiscountQuantit y,
    --p.code
    FROM
    tblShoppingCart s 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


    Tags: None
    • Stephen McCormack
      #2
      Re: Group By????

      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 spGetShoppingCa rts

      @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
      tblShoppingCart s 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 ProductShortTit le,
      sc.quantity,
      TotalPrice = (sc.quantity * p.retailPrice),
      sc.discount,
      sc.code
      FROM
      tblShoppingCart s 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 ProductShortTit le,
      sc.quantity,
      TotalPrice = (sc.quantity * p.retailPrice),
      sc.discount,
      sc.code
      FROM
      tblShoppingCart s 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" <stephenm@mwebs olutions.com.au > wrote in message
      news:XdOMa.235$ JI4.5088@news-server.bigpond. net.au...[color=blue]
      > Hi,
      >
      > I have a pretty simple problem for a SQL programmer regarding GROUPINGS:
      >
      > I have two tables tblShoppingCart s 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 tblShoppingCart s.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
      > spGetShoppingCa rts 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 ,
      > [shortDescriptio n] [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].[tblShoppingCart s] (
      > [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 spGetShoppingCa rts
      >
      > @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 ProductShortTit le,
      > sc.quantity,
      > COUNT(sc.sessio nid) AS ProductCount
      > --p.discount,
      > --p.quantity AS DiscountQuantit y,
      > --p.code
      > FROM
      > tblShoppingCart s 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 ProductShortTit le,
      > sc.quantity,
      > ProductCount = ( SELECT COUNT(sc.produc tid) FROM tblShoppingCart s sc
      > WHERE sc.sessionid = @sessionid )
      > --p.discount,
      > --p.quantity AS DiscountQuantit y,
      > --p.code
      > FROM
      > tblShoppingCart s 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 ProductShortTit le,
      > sc.quantity,
      > COUNT(sc.produc tid) AS ProductCount
      > --p.discount,
      > --p.quantity AS DiscountQuantit y,
      > --p.code
      > FROM
      > tblShoppingCart s 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
      >
      >[/color]


        Comment

        Previous template Next
        Working...