Professional Web Applications Themes

query chalange - Microsoft SQL / MS SQL Server

Hi Freinds, I have a <order> table columns: <orderno><salesman><weekend><sp1><sp2><orderamount > also have 2 other tables <tblsp1> and <tblsp2> which keep the values for <sp1> and <sp2> for <order> table. Like: ORDER <orderno><salesman><weekend><sp1><sp2><orderamount > ---------------------------------------------------------------- 1 1 06/07/03 12 24 12.25 2 1 06/07/03 12 24 14.87 3 1 06/07/03 04 24 15.54 4 1 06/14/03 12 24 11.25 5 1 06/14/03 12 24 12.24 6 1 06/14/03 12 24 24.25 <tblsp1> <sp1> ------- 10 11 12 <tblsp2> <sp2> ------- 21 22 23 24 now want i need it to have sum of the order amount grouping by weekend for only ...

  1. #1

    Default query chalange

    Hi Freinds,
    I have a <order> table
    columns: <orderno><salesman><weekend><sp1><sp2><orderamount >

    also have 2 other tables <tblsp1> and <tblsp2> which keep the values for
    <sp1> and <sp2> for <order> table.

    Like:
    ORDER
    <orderno><salesman><weekend><sp1><sp2><orderamount >
    ----------------------------------------------------------------
    1 1 06/07/03 12 24 12.25
    2 1 06/07/03 12 24 14.87
    3 1 06/07/03 04 24 15.54
    4 1 06/14/03 12 24 11.25
    5 1 06/14/03 12 24 12.24
    6 1 06/14/03 12 24 24.25

    <tblsp1>
    <sp1>
    -------
    10
    11
    12

    <tblsp2>
    <sp2>
    -------
    21
    22
    23
    24

    now want i need it to have sum of the order amount grouping by weekend for
    only those record which have valid entry on tblsp1 and tblsp2. but the point
    is that I need sum of the week if only all weekend is valid against the rule
    for tblsp1.
    so the result should look like:

    <salesman><weekend><orderamount>
    -----------------------------------------
    1 06/14/03 47.74

    thanks in advance,
    Pat


    Patrick Guest

  2. #2

    Default Re: query chalange

    Try this:

    SELECT O.salesman, O.weekend, SUM(O.orderamount) AS orderamount
    FROM Orders AS O
    LEFT JOIN tblsp1 AS T1
    ON O.sp1 = T1.sp1
    LEFT JOIN tblsp2 AS T2
    ON O.sp2 = T2.sp2
    GROUP BY O.salesman, O.weekend
    HAVING COUNT(T1.sp1)=COUNT(*)
    AND COUNT(T2.sp2)=COUNT(*)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  3. #3

    Default Re: query chalange

    This statment will not work for me, because it will sum those record which
    have sp1 valid and not ONLY AND ONLY those for ALL VALID SP1 in all weekend
    like '06/07/03'

    Thanks

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:eASBm$NRDHA.1688TK2MSFTNGP11.phx.gbl...
    > Try this:
    >
    > SELECT O.salesman, O.weekend, SUM(O.orderamount) AS orderamount
    > FROM Orders AS O
    > LEFT JOIN tblsp1 AS T1
    > ON O.sp1 = T1.sp1
    > LEFT JOIN tblsp2 AS T2
    > ON O.sp2 = T2.sp2
    > GROUP BY O.salesman, O.weekend
    > HAVING COUNT(T1.sp1)=COUNT(*)
    > AND COUNT(T2.sp2)=COUNT(*)
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Patrick Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Query of Query LIKE and Wild Card chars
    By Ramboni in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 3rd, 05:09 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

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