[MySQL] Grouping Intervals and counting columns

Say you have table like this...

id	code	dateactivity
1	200	2014/06/04
2	200	2014/05/04
3	500	2014/04/04
4	504	2014/05/28

...and now you want to get the past 7 days but count how many times 200 (success code) comes up and how many times a number other than 200 (failure code) comes up. So the idea is simple. Get total of success + failure codes, left outer join for success, left outer join for failure. The filter where the date is more the the current day minus 7 days. At the end, you should return a row that has the day, total count, success count, and failure count. the Got it? Yes you do. You're a trooper. Here is a sample:

select STR_TO_DATE(a.period, '%m-%d-%y') as period, ifnull(a.cnt,0) total, ifnull(b.cnt,0) success, ifnull(c.cnt,0) failure 
from
(
select
date_format(dateactivity , '%m-%d-%y') period,
count(*) cnt
from mytable
where dateactivity >= DATE_ADD(NOW(), INTERVAL -7 DAY)
group by date_format(dateactivity , '%m-%d-%y')
) a 
left outer join
(
select
date_format(dateactivity , '%m-%d-%y') period,
count(*) cnt
from mytable
where code = '200'
and dateactivity >= DATE_ADD(NOW(), INTERVAL -7 DAY)
group by date_format(dateactivity , '%m-%d-%y')
) b on a.period = b.period 
left outer join
(
select
date_format(dateactivity , '%m-%d-%y') period,
count(*) cnt
from mytable
where (code != '200' or code is null)
and dateactivity >= DATE_ADD(NOW(), INTERVAL -7 DAY)
group by date_format(dateactivity , '%m-%d-%y')
) c on a.period = c.period
;

I know that there are probably different ways of doing this. If you know how, please share. Sharing is caring. And I care for all.

[MySQL] Installing MySql on windows 7 no-installer

Getting the MySQL service to work

• Extract the file contents to desired directory.
• Create a 'my.ini' file in the root of the new directory.
• Copy the following info into my.ini:

[mysqld]
 basedir = C:/mysql
datadir = C:/mysql/data
port = 3306
socket = /tmp/mysql.sock

• In command line, run ' mysqld --install ' to add the MySQL service
• Run MySQL service 
• Then just run mysql to start mysqling. lol

Creating Users

• Run mysql
• Type in: create user 'username'@'localhost' identified by 'password'
• Grant privileges by using the following as guidelines:

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
GRANT USAGE ON *.* TO 'dummy'@'localhost';

[PHP/MySQL] Amazon EC2, Elastic Beanstalk, RDS setup

Basic setup of using these three services in.....haaaarrrmony. I'm using WinXP.

Index:

1. Setup EC2 instance

2. Setup Elastic Beanstalk with GIT (Tested with PHP projects)

3. Setup RDS to be used with MySQL Workbench

1. EC2 Instance

   Just follow the instructions and CREATE and DOWNLOAD your key/pair. Creating your instance is the only time you can assign a key/pair to it, let alone download it. Take note of the security group you are using for it. Also, go find your internet IP and add it to the selected security group.

2. Elastic Beanstalk

   This is basically your web host that you deploy to. Download GIT and the CLI Package (http://aws.amazon.com/code/6752709412171743). Find GIT on Google and the CLI Package in the Elastic Beanstalk documentation. Install GIT and run the CLI Package according to the instructions in the archive. 

Configuring Beanstalk for file uploading/deploying:

Go to your working folder in command line.

First, type eb init

Follow the instructions and you can find the your credentials here: https://aws-portal.amazon.com/gp/aws/securityCredentials

After you're done, enter the command: eb start

Now, you should be able to run git commands. Usually, this is the sequence I use:

1. git add .    (adds all files/folders/sub-folders)

2. git commit -m "<your message>"

3. git aws.push

3. RDS

1. Choose/create a database instance you would like to work with.

2. Go to the DB Security Groups section.

3. Add a new connection type of EC2 Security Group and choose the same one used in your EC2 Instance. You may also want to add a new CIDR/IP address using an IP finder.

-- MySQL Workbench

4. Setup a New Server Instance

5. Select Remote Host and input the address of your EC2 instance.

6. Select SSH type of connection

7. For SSH user, put ec2-user if using Amazon or ubuntu if using Linux

8. SSH password is not required.

9. Use the key/pair generated earlier

10. Enter the rest with the DB Instance info.

After all that, you should be good to go. Cheers

[SQL/MySQL/.NET] Setting databases for .NET usage

After setting up WCF services and having my android devices consuming it, I wanted my WCF to be the layer that communicates to a database resource. At first, I had a hard time with getting started with SQL Server, so I went with MySQL instead.

NOTE: In your IIS, make sure the ASP.NET 4.0 Application Pool "Identity" is set to Local System. This is the reason why SQL Server wouldn't go so smooth at first, but I will discuss details later.

MySQL setup:

1. Download MySQL server installer, MySQL OBDC connector, and MySQL Net connector. Optionally, download the Workbench, which provides a GUI solution for handling SQL functions. If you're lame like me, you would use this tool.

2. Set up your tables using Workbench or commandline.

3. In Visual Studio, add a new ADO.NET Entity Data Model and point to your MySQL Server when prompted. If you don't know the server name or IP Addy, you can always go to the Workbench and see the properties there. Mine was 127.0.0.1. Follow through with creating the .edmx file.

4. Now you should see the newly created entities right in front of you. The name of the object is exactly what the header of the entity is. So you can instantiate it like:

User objUser = new User();

Of course, we don't want to instantiate like this, but rather grab the info from the database and create the object then. We will use LINQ to do so.

 Now look for Entity Container Name in the properties of your .edmx file. This is the object you use to connect to the database. So lets just put 2 and 2 together with the entity object included:

MyDBEntities db = new MyDBEntities();
List<User> users = ( from p in db.Users select p ).ToList();

This should select all users from a "Users" table and return it as a list of User objects.

Run the WCF Test Client and test if it works.

SQL Server setup:

Okay, if you installed SQL Server 2008, I believe that it should've created an instance of SQLEXPRESS. So we will just go with this default setup, although, you might have set up another instance, you'll just have to remember the server name for that one.

So by default, your instance of SQLEXPRESS should be your <computername>\SQLEXPRESS. (ex. KETTLEPOT-PC\SQLEXPRESS).

Create tables in SQL Management Studio (Free) by using the server name.

From now on, follow step 3 from the MySQL setup section to configure everything in VS2010.

 Now, if you're getting the "underlying provider" error when you run the WCF Test Client and invoke your method, check out the "NOTE" at the beginning of this post.

Hope that helps!!!!