Jump to content

MySQL query help


Tauheed
 Share

Recommended Posts

I don't have MySQL experience, I need some help to write a query for my work. I need a query that count transaction for a week, for example, I wanna run the query every Friday and want to see the result from last Friday to Thursday, I mean just last week's data. The start date is last Friday (11/12/2021) and the End date is Thursday (11/18/2021). It is a weekly process that I can run every week to download the output. 

There are two tables, sales, and customers. The customer table has fields such as "customer_id", FirstName, LastName, etc and the sales table has "id" which is the same as customer_id from customer table, salesDate, etc. 

I want to count data of those who are customer_id match to the sales table. I would like to ignore some of the users as well like our IT team has a couple of test users that uses for testing. for example testuser1 and testuser2. I would like the query to not count these two users.

Here is an example that I want to make work. Can someone please help me to write the query? 

SELECT COUNT(CustomerID) AS NumberOfCustomer FROM Customer
INNER JOIN Sales ON ID = CustomerID
where user != (testuser1 and testuser2
where sales date between last Friday to Thursday;

 

Thanks!

 

Link to comment
Share on other sites

What kind of duplication do you have in both tables?

 

 

 

 

Edited by niche
Link to comment
Share on other sites

15 hours ago, niche said:

What kind of duplication do you have in both tables?

 

 

 

 

ID from the sales table and customer id from the customer table are the same. Customer ID is primary key in customer table and id is foreign key in sales table. Thanks 

Link to comment
Share on other sites

If you want to do a COUNT, one of both tables have some dupes.  Which ones or both? 

Edited by niche
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...