Professional Web Applications Themes

GROUP BY?? Simple! - Microsoft SQL / MS SQL Server

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] ...

  1. #1

    Default GROUP BY?? Simple!

    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 Guest

  2. #2

    Default Re: GROUP BY?? Simple!

    When you use GROUP BY, you will ideally expect one row per record that you
    group by. Therefore it is normal to use aggregate functions like SUM, COUNT
    with GROUP BY. In your case, since in the GROUP BY expression, you have
    specified all the columns, you might actually end up getting the same
    content as the original table.

    What is the output that you want by using the GROUP BY. This will help us to
    provide alternate solutions.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "Stephen McCormack" <stephenmmwebsolutions.com.au> wrote in message
    news:STOMa.254$JI4.5598news-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
    >
    >
    >

    SriSamp Guest

  3. #3

    Default Re: GROUP BY?? Simple!

    Your immediate problem is that you don't understand what a GROUP BY
    does. Here is how a SELECT works in SQL ... at least in theory. Real
    products will optimize things when they can.

    a) Start in the FROM clause and build a working table from all of the
    joins, unions, intersections, and whatever other table constructors
    are there. The table expression> AS <correlation name> option allows
    you give a name to this working table which you then have to use for
    the rest of the containing query.

    b) Go to the WHERE clause and remove rows that do not pass criteria;
    that is, that do not test to TRUE (reject UNKNOWN and FALSE). The
    WHERE clause is applied to the working in the FROM clause.

    c) Go to the optional GROUP BY clause, make groups and reduce each
    group to a single row, replacing the original working table with the
    new grouped table. The rows of a grouped table must be group
    characteristics: (1) a grouping column (2) a statistic about the group
    (i.e. aggregate functions) (3) a function or (4) an expression made up
    of the those three items.

    d) Go to the optional HAVING clause and apply it against the grouped
    working table; if there was no GROUP BY clause, treat the entire table
    as one group.

    e) Go to the SELECT clause and construct the expressions in the list.
    This means that the scalar subqueries, function calls and expressions
    in the SELECT are done after all the other clauses are done. The AS
    operator can give a name to expressions in the SELECT list, too.
    These new names come into existence all at once, but after the WHERE
    clause, GROUP BY clause and HAVING clause has been executed; you
    cannot use them in the SELECT list or the WHERE clause for that
    reason.

    If there is a SELECT DISTINCT, then redundant duplicate rows are
    removed. For purposes of defining a duplicate row, NULLs are treated
    as matching (just like in the GROUP BY).

    f) Nested query expressions follow the usual scoping rules you would
    expect from a block structured language like C, Pascal, Algol, etc.
    Namely, the innermost queries can reference columns and tables in the
    queries in which they are contained.

    You also have some classic flaws that will give you long term
    problems, like using "id" and "product_id" for the same data element
    -- doesn't your product use a ISBN, UPC or other industry standard
    code? There is no such thing as just an "id" -- it is the identifer
    of something (might want to reads the iSO-11179 Standards).

    What good does the BIT flag about images do us? Why not tell the
    program the location of this image, so it can use it? Why is your
    logic in the proprietary, procedural T-SQL instead of using CASE
    expressions and OUTER joins to this in one SELECT statement? Etc.
    --CELKO-- Guest

Similar Threads

  1. Need Simple Answer to Simple Contribute/Firefox question
    By Adobe nickname in forum Macromedia Contribute General Discussion
    Replies: 2
    Last Post: February 13th, 06:59 PM
  2. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  3. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  4. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  5. XP Simple File Sharing and Group Policy
    By Dave Branscome in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 11th, 11:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139