Professional Web Applications Themes

union and count - MySQL

Hi all, I've 3 tables. The first is a table of customers, the second a table of orders and the third a table of questions. I've to provide a table with the customerid, the number of orders and the number of questions. For every order and every question I've a new record in those table. The tables are quite huge, so I can't send the structure but basically in the 3 tables they are the customerid number to link for. As I general idea I'd like something like: select name from customer, count(ordersid) from orders, count (questionid) from customer left ...

  1. #1

    Default union and count

    Hi all,

    I've 3 tables. The first is a table of customers, the second a table of
    orders and the third a table of questions.

    I've to provide a table with the customerid, the number of orders and the
    number of questions.
    For every order and every question I've a new record in those table. The
    tables are quite huge, so I can't send the structure but basically in the 3
    tables they are the customerid number to link for.

    As I general idea I'd like something like:

    select name from customer, count(ordersid) from orders, count (questionid)
    from customer
    left join orders on customer.customerid = order.customerid
    left join questions on customer.customerid = questions.customerid

    Is this possible ? how ?


    Bob Guest

  2. #2

    Default Re: union and count

    "Bob Bedford" <com> a écrit dans le message de
    news:467a93d0$0$3789$sunrise.ch... 

    try to add "group by customerid"
    (look at this recent thread "speedest way to handle a select
    table1,count(table2) where table1.idtable1=table2.idtable1 ?")


    patrice Guest

  3. #3

    Default Re: union and count


    "patrice" <fr> a écrit dans le message de
    news: 467aa3ca$0$19103$free.fr... 
    >
    > try to add "group by customerid"
    > (look at this recent thread "speedest way to handle a select
    > table1,count(table2) where table1.idtable1=table2.idtable1 ?")[/ref]

    it works when I've only one count(), but if I've 2 counts from 2 diffent
    tables, it multiplies the result of the first count by the result of the
    second

    I've 2 tables from wich I must do a count and I can't get a correct result.

    Bob


    Bob Guest

  4. #4

    Default Re: union and count

    On Thu, 21 Jun 2007 17:05:15 +0200, "Bob Bedford" wrote:
     

    Using "virtual tables", something like:

    Select c.`name`, o.orders, q.questions
    From customer c
    Join (
    Select customerid, count(ordersid) As orders
    From orders
    Group By customerid
    ) o On c.customerid = o.customerid,
    Join (
    Select customerid, count(questionid) As questions
    From questions
    Group By customerid
    ) q On c.customerid = q.customerid
    Order By c.`name`

    NB: just typed it in, you will need to play with it / fix it yourself.
    --
    Ross McKay, Toronto, NSW Australia
    "Let the laddie play wi the knife - he'll learn"
    - The Wee Book of Calvin
    Ross Guest

  5. #5

    Default Re: union and count

    > Using "virtual tables", something like: 

    Splendid Mate ;-) !!! works fine.

    Thanks a lot !


    Bob Guest

Similar Threads

  1. Count from a union
    By Derek Fountain in forum MySQL
    Replies: 2
    Last Post: August 18th, 09:15 AM
  2. Union
    By denis in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 15th, 04:49 PM
  3. How use UNION with Sum() ???
    By lubiel in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 22nd, 02:34 PM
  4. About 'Union'
    By Hawk in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 06:11 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