StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POComplex SQL Select Statement
text
Body
copied!<p>I'm attempting to create a SQL statement that does the following but obviously failing at it.</p> <p>Selects all the information from a view and filters it based on the following criteria:</p> <ol> <li>If the given field "customer_number" exists at any point in the table</li> <li>Then check for any duplicates within the last 30 days</li> <li>Then output all distinct customer_numbers grouped by customer_number</li> <li>Have a count so that I can see how many times an item was repeated.</li> </ol> <p>So using today as our current date say I had the following data entries in my table:</p> <pre><code>customer_number, date 111111, 2013-01-01 111111, 2013-05-05 222222, 2013-01-07 222222, 2013-03-19 333333, 2013-05-15 333333, 2013-05-30 </code></pre> <p>I would want my SELECT statement to return the following:</p> <pre><code>customer_number, date, count 111111, 2013-01-01, 2 333333, 2013-05-15, 2 </code></pre> <p>And customer 222222 would not be displayed because he does not appear in the last 30 days.</p> <p>Here's about as far as I've gotten trying to figure this out. It only returns data for the current month.</p> <pre><code>SELECT *, COUNT(customer_number) FROM red_flags WHERE dealer_id = '75' AND date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() GROUP BY customer_number HAVING COUNT(customer_number) > 1 </code></pre> <p>I've been melting my brain trying to figure out how or if this is even possible to do in a query any help would be greatly appreciated!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload