Professional Web Applications Themes

Query database fro new customers - Coldfusion Database Access

I'd like to query a sales table to display only customers who have bought for the first time. Any Ideas?...

Sponsored Links
  1. #1

    Default Query database fro new customers

    I'd like to query a sales table to display only customers who have bought for the first time. Any Ideas?
    Sponsored Links
    cactusbob Guest

  2. #2

    Default Re: Query database fro new customers

    cactusbob wrote: 


    lots.
    you can do it like this
    or like this
    or like this.

    no, really, how do you expect any help when you don't provide any useful
    info?

    to start with: what's the structure of your sales table? what type of
    db? which CF version?
    --

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com
    Azadi Guest

  3. #3

    Default Re: Query database fro new customers

    I f you keep a seperate record for each transaction per customer simply query
    for DISTINCT users in the transaction table and there's your single time
    buyers. Or create another table (NEW CUSTOMER) with a key to the customer table
    and populate it when a new customer completes a transaction. That way you can
    remove records from that NEW CUSTOMER table after you do whatever with them.

    bigbrain28 Guest

  4. #4

    Default Re: Query database fro new customers

    bigbrain28 wrote: 

    wrong. using SELECT DISTINCT will return unique customers, basically
    returning each customer once in the recordset, even if a customer has
    made purchases a million times. it will NOT return customers who bought
    only once.

    --
    Azadi Saryev
    Sabai-dee.com
    Vientiane, Laos
    http://www.sabai-dee.com
    Azadi Guest

  5. #5

    Default Re: Query database fro new customers

    I agree that SELECT DISTINCT will return all customers...

    I use MYSQL and CF 5. The sales table includes sale date, customer name and a
    unique invoice number which relates to s separate table with item details for
    the sale. I can cfloop through a query and do a second query looking for the
    amount of records for a given customer, but I would prefer to do it in a single
    query.

    I want to query the table once a month to identify new customers

    Bob

    cactusbob Guest

  6. #6

    Default Re: Query database fro new customers

    Bob,

    I believe that using COUNT AND HAVING in your SQL may be the way get you where
    you need to be.

    Given your table information, I could use a query like the following to grab
    only customers with a single entry in the sales table (versus multiple entries
    for multiple orders):

    <cfquery name="qryTest" datasource="dsn">
    select customerName, count(customerName)
    from sales
    group by customerName
    HAVING count(customerName) > 1
    </cfquery>

    This should only pull customers who have one entry in the table.

    craigkaminsky Guest

  7. #7

    Default Re: Query database fro new customers

    Thanks Craig

    That's the ticket!

    Bob
    cactusbob Guest

  8. #8

    Default Re: Query database fro new customers

    Bob,

    Just realized I have a typo in my code. You probably know this already but change the greater than symbol to an equal sign...a little duh, duh on my part there. Sorry!
    craigkaminsky Guest

  9. #9

    Default Re: Query database fro new customers

    Craig, I did realize that and added a date filter

    That's a much more elegant solution than doing a double query, thanks again

    Bob
    cactusbob Guest

Similar Threads

  1. Two database query
    By MartinThorpe in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 3rd, 07:01 PM
  2. Need help with database query.
    By Trenchy in forum Coldfusion Database Access
    Replies: 9
    Last Post: March 31st, 03:12 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