Customer and branch query in company database

I have following four tables in my database :

EMPLOYEE

PK: employeeID;     
FK: empBranch references BRANCH;
FK: empSupervisor references EMPLOYEE

BRANCH

PK: branchNumber
FK: branchManager references EMPLOYEE

CUSTOMER

PK: customerID

ORDERS

PK: orderNumber
FK: customerID references CUSTOMER; 
FK: salesPerson references EMPLOYEE

Now I need to list out these two things :

1. The Employee_ID and name of salespersons who have sold only to customers located in the same city as the city in which the salesperson’s branch is located.

2. The Employee_ID and name of salespersons who have sold to every customer located in the same city as the city in which the salesperson’s branch is located.

Please help me to answer both queries

Split into different columns SQL Server

I've got data in a column of my table just like this:

cpc > cpc > organic
cpc > organic >cpc
mail > cpc > organic

How can I split that data to different columns using '>' as a delimiter?

Transform and copy table data

Sorry if this questioned was asked before, but i am posting it, since was not able to find anything related in forum. So any help would be appreciated. Here is my scenario.

We have an SQL table view A (just read access) with set of data located on Server A on domain A. We have our SQL database with table B located on Server B on Domain B. We have got the port opened up between 2 domains.

We need to write a SSIS job (runs per day) which will get the data from View A and insert in Table B. So what could be the best way for achieving this.

Note:

• table View on server doesn't have deleted or last modified columns So wont be able to figure out what are the changes. (if there is any other way please do let me know)
• Database data is around 50k records

Approach 1:

• Export data from View
• Transform it
• Drop table on Server B
• Insert the transformed data

Approach 2:

• Export data from view
• Transform it
• Insert into a temp table
• Make a log entry into log table
• When the log entry says successfully copied, trigger to delete all the rows and copy the data from temp table to Table B.
• If something went wrong, rollback.

Grouping based on the order of data

I have a set of data in the following format.

A
A
B
A
C
B
B
C

I want to group the data in such a way that the result should be grouped by alphabet and is should be based on order. For example for the above data the output should be

A - 2
B - 1
A - 1
C - 1
B - 2
C - 1

mysql to search in multiple quantity

I have a problem. I'm building a Client Information System with PHP and MYSQL. One of the feature of the application is to allow users to search for the name by typing ic number in multiple input. For example, information stored in the database:

ID : 1
NAME : John
IC : 111

ID : 2
NAME : James
IC : 222

ID : 3
NAME : Sarah
IC : 333

---

So, to search the data, in search field (form), i would like to type 111222 , without comma. so is there any ways? Any sql syntax which should i use?

Any help is very much appreciated

---

EDITED

Here is my code for searching function.

$ics = $_POST["nokp"]; 
$each =explode(' ',$ics);

$q=mysql_query("SELECT id,nama,nokp from client where nokp in(". implode(',', $each).") group by nama,nokp");

c# change primary key

I need to change a column type in a SQL Server table.

I need do it using a script or C#.

The original datatype is integer and the new values is varchar(50).

I can do it using this SQL script:

ALTER TABLE confezionamento 
  ALTER COLUMN partnumber varchar(50)   

but the problem is that this column is into a set of primary key.

When I try do execute, an error occurred because PK_CONFEZIONAMENTO use the column.

How I can do it? I can't access by SQL Server Management Studio.

SQL query returning events with an odd number of users assigned to them

I have 5 tables--users, groups, events, users_groups_link, users_events_link. Like so:

users
user_id

groups
group_id

events
event_id

users_groups_link
user_id
group_id

users_events_link
user_id
event_id

Each event has 2 or 4 groups assigned to it and each group contains 8-11 users. user_id=1 is a group vacancy. I'm wanting to make sure that each event has an even number of users so I'm trying to create a query that returns all events containing an odd number of users. So far I have this:

SELECT user_id,event_id 
FROM users,groups,events,users_events_link,users_groups_link
WHERE users.user_id=users_events_link.user_id
AND events.event_id=users_events_link.event_id
AND users.user_id=users_groups_link.user_id
AND groups.group_id=users_groups_link.group_id
AND user_id <> 1

which returns all events and users that are assigned to them (via their host group) like so:

user_id         event_id
56              1001
34              1001
76              1001
45              1001
87              1001
88              1001
54              1001
4               1001
63              1002
69              1002
77              1002
etc.

Now I'm wanting to refine this query further by just returning events with an odd number of users assigned to them i.e. events with their id in the right hand column repeating an odd number of times. And then maybe refining it further by eliminating the users_id column entirely and returning one event id for each event containing an odd number of users.

I've tried using "count()" and "group by" but am not getting the desired results so i'm obviously doing something wrong but I don't know what. Thanks in advance.

SQL Compare rows

Okay here is the situation: I the following data in a table.

PAIR_NO NO          NO2

3       5678EFGH    1234ABCD
4       1111BBBB    0000AAAA
1       1234ABCD    5678EFGH
2       0000AAAA    1111BBBB

The constraints are if no = no2 in another row skip that row. So in this sample data the only rows that would be selected should be pair no 3 and 4.

I have tried to merge and inner join with self but I just keep getting all 4 rows back.

I have tried to insert into a table where not exists but again I get 4 rows inserted.

   SELECT a.* from PAIRS a
   inner join PAIRS b on a.no=b.no2 and a.no2=b.no;

I was thinking maybe selecting distinct number from column 1 and then check those in column 2 but I think that would yield the same four rows.

I may be over thinking this problem and maybe some here can look at this and see where the solution is hiding.

I am currently testing this on MySQL but it should run on SQLServer 2008. I have searched but all the questions didn't seem to match my data set issue.

SQL - count relation instances

I have this SQL diagram:

I want to get all the subscribers that likes exactly 0 reports.

started by: SELECT * FROM subscriber HAVING count(...)

How do I count how many reporters a subscriber likes?

A relationship should get it's own table in an SQL database?

SQL intersect with group by

Given these two tables/sets with different groups of items, how can I find which groups in set1 span across more than a single group in set2? how can I find the groups in set1 which cannot be covered by a single group in set2?

e.g. for tables below, A (1,2,5) is the only group that spans across s1(1,2,3) and s2(2,3,4,5). B and C are not the answers because both are covered in a single group s2.

I would prefer to use SQL (Sql Server 2008 R2 available).

Thanks.

set1                            set2
 +---------+----------+          +---------+----------+
 | group   |  item    |          | group   |  item    |
 `````````````````````+          `````````````````````+
 |   A     |    1     |          |   s1    |    1     |
 |   A     |    2     |          |   s1    |    2     |
 |   A     |    5     |          |   s1    |    3     |
 |   B     |    4     |          |   s2    |    2     |
 |   B     |    5     |          |   s2    |    3     |
 |   C     |    3     |          |   s2    |    4     |
 |   C     |    5     |          |   s2    |    5     |
 +---------+----------+          +---------+----------+

Use this sqlfiddle to try: http://ift.tt/1b1mvOi

Or use the script below to generate temp tables to try out the answers:

create table #set1 (grp varchar(5),item int)
create table #set2 (grp varchar(5),item int)

insert into #set1 select 'a',1 union select 'a',2 union select 'a',5 union select 'b',4 union select 'b',5 union select 'c',3 union select 'c',5
insert into #set2 select 's1',1 union select 's1',2 union select 's1',3 union select 's2',2 union select 's2',3 union select 's2',4 union select 's2',5

select * from #set1
select * from #set2

--drop table #set1
--drop table #set2

Understanding DELETE and DROP command

I a student of Computer Science. Recently I am trying to learn database. I found a link describing about the difference between DELETE and DROP command. I understand most of the difference between them with the most important one - DELETE removes only data where DROP removes the table/object too.

But I can not understand one difference - DELETE command fire a DML trigger where DROP doesn't fire any DML trigger. The link only state this fact without any explanation. Can someone explain it a bit, so that I can understand it more.

Thanks in advance.

Looping through fields in one table to find records from another table [on hold]

Here's what I'm trying to do: I have a lookup table im SQL with a field that contains filename. I need to iterate through each of the records in this table and find the associated records from another table (using the filename column as the key field) and then save each set of matching records as a CSV file locally on my computer. I tried the below cursor and it's not working. Can someone assist?

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path to save file
DECLARE @fileName VARCHAR(256) -- filename for output file DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Output\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT * FROM dbo.filenamelookup fl JOIN dbo.recordstable rt ON fl.filename = rt.filename OPEN db_cursor
FETCH NEXT FROM db_cursor INTO #temp

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.csv'
BACKUP DATABASE #temp TO DISK = @fileName

   FETCH NEXT FROM db_cursor INTO @name   

END

CLOSE db_cursor
DEALLOCATE db_cursor

Trigger compilation error

Hello I need some help in creating my trigger here is my trigger please tell me which part of my code is wrong so I could work on it, Thanks

create or replace trigger trigger_one 
before insert on Funtom_timesheet
for each row
Declare
V_id    number;
V_hours number;
Begin
Select max(timesheet_ID)+1 into v_id from Funtom_timesheet
:new.timesheet_ID :=v_id;
select grade_hours into V_hours
from funtom_grade join funtom_employee
on emp_grade = grade_id
where empid = :new.timesheet_emp;
if V_hours >:new.timesheet_hours
else
:new.timesheet_overtime :=
:new.timesheet_hours-V_hours
:new.timesheet_hours:= V_hours;
END IF;
END;
/

What is output of this query in SQL…Select INSTR('ORACLE REPORT,'OR', -3,2) "INSTRING " FROM DUAL;

Select INSTR('ORACLE REPORT,'OR', -3,2) "INSTRING " FROM DUAL;

Django - How to link tables

Hello to the stackoverflow team,

I have the following two django tables:

class StraightredFixture(models.Model):
    fixtureid = models.IntegerField(primary_key=True)
    soccerseason = models.IntegerField(db_column='soccerSeason')  # Field name made lowercase.
    hometeamid = models.IntegerField()
    awayteamid = models.IntegerField()
    fixturedate = models.DateTimeField()
    fixturestatus = models.CharField(max_length=24)
    fixturematchday = models.IntegerField()
    hometeamscore = models.IntegerField()
    awayteamscore = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'straightred_fixture'

class StraightredTeam(models.Model):
    teamid = models.IntegerField(primary_key=True)
    teamname = models.CharField(max_length=36)
    teamcode = models.CharField(max_length=5)
    teamshortname = models.CharField(max_length=24)

    class Meta:
        managed = False
        db_table = 'straightred_team'

In the views.py I know I can put the following and it works perfectly:

def test(request):
    fixture = StraightredFixture.objects.get(fixtureid=136697)
    return render(request,'straightred/test.html',{'name':fixture.hometeamid})

As I mentioned above, this all works well but I am looking to return the teamname of the hometeamid which can be found in the StraightredTeam model.

After some looking around I have been nudged in the direction of "select_related" but I am unclear on how to implement it in my existing tables and also if it is the most efficient way for this type of query. It feels right.

Any advice at this stage would be greatly appreciated. Many thanks, Alan.

Query to get number of stars = length of string in the next row in Oracle 10g

is there any SQL query in Oracle10G which can give the desired output as given required in below sample. Query should print the name first and in the second row it should print the stars equal in number with the length of the string.

Could you please help?

Below is the sample of table column

JIM JOHN MICHAEL

and the output should come like below :

JIM

---

JOHN

---

MICHAEL

---

How can I update a table based on a plpgsql instruction loop on PostgreSQL 8.3.16?

I got a problem I not sure how to solve it so far...

I have two tables that are related to each other with a 1 x n relation. I will try to describe the more importants fields below:

Table One - company:id PK,companyname varchar;
Table Two - training: course varchar,companyid bigint FK,id PK;

The problem is: I would like to update the information on course field of the table training because there are many courses with the same name. My idea is use something like

for s in 1..n loop 
update training set course = course || s;
end loop;

SQL query doesn't work?

I want to create a table in the database and below is the query that I wrote; does anyone have any idea why it doesn't work?

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sname varchar(255), city varchar(255), avg int(20), clg# int(20) )' at line 4`

CREATE TABLE stud
(
s# int,
sname varchar(255),
city varchar(255),
avg int(20),
clg# int(20)
);

Average Hour of Day in Teradata

I have a table with hundreds of timestamps.

I extract the hour, then I can simply average that, however, this is where I get issues.

select purchase,
CAST(AVG(EXTRACT(HOUR FROM opened_at)) AS INT) as average_open
            from db.purchasetable 
group by 1

When I average hour 22 and hour 2 I get 12(obviously), however, what I want is 0 cause midnight is the average point in time between the two hours.

How do I do this in Teradata?

Find a string in a specific table

I'm working with a oracle database, I'm wondering if there is a way to find all rows that contains a value in any column. For example let's consider this table:

               Weather

city          state        high    low

Phoenix       Arizona      105     90

Tucson        Arizona      101     92

Flagstaff     Arizona      88      69

San Diego     California   77      60

Albuquerque   New Mexico   80      72

Basically (I know it's not possible), but I would like to do something like this:

Select * From Weather Where * LIKE '%f'

and it would give me the rows

Flagstaff     Arizona      88      69
San Diego     California   77      60

I could do it on the Java side, by querying all the rows then with the ResultSet dynamically search for a given value in a column and add that row. The problem is that some table contains millions of rows and I guess it would be more efficient do it on the database side, so I only fetch wanted rows from the network.

Is it possible to do it on the SQL side directly?

How to write a query with count

I have two tables as follows:

==================
StudentsClasses  
----------------
ID (Registration ID of the class)
StudentID (ID of student taking class)
ClassID (ID of certain class)
----------------
==================

Students
---------------
ID (ID of student)
Name (Name of student)
GradeLevelID (Grade of student)
---------------
==================

And they are joined by StudentsClasses.StudentID and Students.ID.

I am trying to write a query to return the students with the least classes registered. My query is:

SELECT Students.Name, COUNT(StudentsClasses.StudentID) AS Expr1
FROM     StudentsClasses INNER JOIN
                  Students ON StudentsClasses.StudentID = Students.ID
GROUP BY StudentsClasses.StudentID, Students.Name
ORDER BY Expr1

However, that only returns all the students with at least 1 class in ASC order.

I know the correct answer is 7 students with 0 classes.

How can I modify my query to return only those 7 students with 0 classes.

Invalid identifier error when all listed elements exist

I'm doing a basic database assignment on ETL. I'm trying to update a column by referencing three distinct columns in two other tables.

I am getting this error report in SQL Developer:

SQL Error: ORA-00904: "DIMTIME"."DAY_TIME": invalid identifier
00904. 00000 -  "%s: invalid identifier"` 

I feel my syntax is bad as all listed elements exist.

Code snippet is:

update fact_stage set date_sk = (
  select date_sk from time_stage
  where (time_stage.year_time = dimtime.year_time)
  and (time_stage.month_time = dimtime.month_time)
  and (time_stage.day_time = dimtime.day_time)
); 

Filtering by analytic function results without subquery or subtable

I'm working on Netsuite project which has limited SQL capabilites. It's difficult to test as I am basically guessing the SQL they are building in their GUI.

I'd like to filter the results of a query to the results with a negative value of a culmative sum.

Is the following a valid PL/SQL construct (barring any small syntactical errors)?

 SELECT SUM(amount) OVER(PARTITION BY itemid ORDER BY date ROWS UNBOUNDED
    PRECEDING) AS "sum" FROM table WHERE sum < 0

Secondly, due to limitations in Netsuite, is the following a valid construct?

 SELECT SUM(amount) OVER(PARTITION BY itemid ORDER BY date ROWS UNBOUNDED 
    PRECEDING) AS "sum" FROM table WHERE SUM(amount) OVER(PARTITION BY itemid
    ORDER BY date ROWS UNBOUNDED PRECEDING) < 0

Oracle's documentation suggests that neither of these are valid and filtering an analytic function should be done via subquery but some google groups and other websites suggest otherwise. Most however are using RANK() and DENSE_RANK() functions in their examples which may function differently.

sql syntax error

hi dears i have a trouble problem in my query, please help me thanks for helping.

 $this->db->select("teacher.teacher_id,name,lname,father_name,salary_per_hour,monthly_salary,"
                    . "(select ($user_month - month(salary_start_date)) from teacher)as deff_month ,"
                    . "(select ADDDATE(salary_start_date, interval deff_month month) from teacher) as start_interval,"
                    . " (select ADDDATE(start_interval, interval 1 month)) as end_interval ,"
                    . "(select DATE_SUB(start_interval, interval 1 month)) as reverse_end_interval,"
                    . "(select date($miladi)) as user_date,(select sum(hours) from teacher_hours_late where (date>start_interval AND date<=end_interval AND start_interval<$miladi ) OR (date<=start_interval AND date>reverse_end_interval AND start_interval>=$miladi )) as total_late");
            $this->db->from('teacher');
            $this->db->join('teacher_hours_late',"teacher.teacher_id=teacher_hours_late.teacher_id", 'inner');

            $this->db->where('teacher.branch_id',$branch_id);
            $query=$this->db->get();
            return $query->result();

Rollback and Scheduling in Database?

If we use the Timestamp Ordering for concurrency control in following scheduling:

My TA says T2,T3,T5 is Run and T4,T1 is Rollback. I think it's false. any expert could help us? (i.e, in this schedule which of the Transaction Rollback and which one Is Done?

Update: All transaction after doing all work, commits.

Reading integers from SQLite database returns 0?

In my Android application, I have an SQLite database that stores a students grade information. I am able to read in the category of the grade correctly but for some reason when i try to read in the grade itself resCurs.getInt(gradeIndex) returns 0 every time even though the grade value in my database is clearly not 0. Any help would be greatly appreciated as I see nothing wrong with my code

GradeDBOpenHelper.java

package bcs421.jorgeramirez.hwk.gradeapp.adv;

 import android.content.Context;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteDatabase.CursorFactory;
 import android.database.sqlite.SQLiteOpenHelper;

 public class GradeDBOpenHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "GradeData.db";
public static final String DATABASE_TABLE = "GradeData";
public static final int DATABASE_VERSION = 1;

public static final String KEY_ID = "_id";
public static final String COLUMN_CAT = "category";
public static final String COLUMN_NUM = "itemnumber";
public static final String COLUMN_DESC = "description";
public static final String COLUMN_GRD = "grade";
public static final String COLUMN_DATE = "date";

static final String DATABASE_CREATE =
        "CREATE TABLE " + DATABASE_TABLE +  "(" + 
        KEY_ID + " integer primary key autoincrement, " +
        COLUMN_CAT + " varchar(20), " +
        COLUMN_NUM + " integer, " +
        COLUMN_DESC + " varchar(255), " +
        COLUMN_GRD + " integer, " +
        COLUMN_DATE + " varchar(255));";

private static final String STUDENT_SELECT =
        "SELECT * FROM " + DATABASE_TABLE + ";";

static final String GRADE_INSERT_1 =
        "INSERT INTO " + DATABASE_TABLE + " VALUES (NULL, 'Homework', 1, 'Assigment 1 - Schedule App', 100, '02/10/2015');";

static final String GRADE_INSERT_2 =
        "INSERT INTO " + DATABASE_TABLE + " VALUES (NULL, 'Quiz', 1, 'Quiz - Syllabus', 100, '02/10/2015');";

static final String GRADE_INSERT_3 =
        "INSERT INTO " + DATABASE_TABLE + " VALUES (NULL, 'Lab', 1, 'Lab Ch1 - Hello Yankees', 100, '01/28/2015');";

static final String GRADE_INSERT_4 =
        "INSERT INTO " + DATABASE_TABLE + " VALUES (NULL, 'Lab', 2, 'Lab - Hello World', 100, '02/04/2015');";

static final String GRADE_INSERT_5 =
        "INSERT INTO " + DATABASE_TABLE + " VALUES (NULL, 'Lab', 3, 'Lab - Manifest and Different Screens', 0, '02/09/2015');";







public GradeDBOpenHelper(Context context, String name, CursorFactory factory, int version) {
    super(context, name, factory, version);

}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(DATABASE_CREATE);
    db.execSQL(GRADE_INSERT_1);
    db.execSQL(GRADE_INSERT_2);
    db.execSQL(GRADE_INSERT_3);
    db.execSQL(GRADE_INSERT_4);
    db.execSQL(GRADE_INSERT_5);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub

}


}

GradeContentProvider.java

 package bcs421.jorgeramirez.hwk.gradeapp.adv;

 import android.content.ContentProvider;
 import android.content.ContentUris;
 import android.content.ContentValues;
 import android.content.UriMatcher;
 import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteQueryBuilder;
 import android.net.Uri;

 public class GradeContentProvider extends ContentProvider {


private GradeDBOpenHelper gradeDatabase;

public static final Uri CONTENT_URI = Uri.parse("content://bcs421.jorgeramirez.hwk.gradeapp");

private static final int ALLROWS = 1;
private static final int SINGLE_ROW = 2;

private static final UriMatcher uriMatcher;
static {
    uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
    uriMatcher.addURI("bcs421.jorgeramirez.hwk.gradeapp", "locations", ALLROWS);
    uriMatcher.addURI("bcs421.jorgeramirez.hwk.gradeapp/#", "locations", SINGLE_ROW);
}


@Override
public boolean onCreate() {
    //*******************************************
    // Construct the underlying database.
    // It will defer opening the datbase until you need to
    // perform a query or transaction. 
    //*******************************************

    this.gradeDatabase = new GradeDBOpenHelper(getContext(),
            GradeDBOpenHelper.DATABASE_NAME, null,
            GradeDBOpenHelper.DATABASE_VERSION);

    return true;
}

@Override
public Cursor query(Uri uri, String[] projection, String selection,
        String[] selectionArgs, String sortOrder) {
    SQLiteDatabase db = this.gradeDatabase.getReadableDatabase();

    String groupBy = null;
    String having = null;

    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    queryBuilder.setTables(GradeDBOpenHelper.DATABASE_TABLE);

    switch (uriMatcher.match(uri)) {
        case SINGLE_ROW:
            String rowID = uri.getPathSegments().get(1);
            queryBuilder.appendWhere(GradeDBOpenHelper.KEY_ID + " = " + rowID);
            break;
        default:
            break;
    }

    Cursor cursor = queryBuilder.query(db, projection, selection, selectionArgs, groupBy, having, sortOrder);
    return cursor;
}

@Override
public String getType(Uri uri) {
    // TODO Auto-generated method stub
    return null;
}

@Override
public Uri insert(Uri uri, ContentValues values) {
    SQLiteDatabase db;

    db = this.gradeDatabase.getWritableDatabase();

    // Actually insert the new zip in the DB
    long id = db.insert(GradeDBOpenHelper.DATABASE_TABLE, null, values);

    if (id > -1)
    {
        // Construct and return the URI of the newly inserted row
        Uri insertId = ContentUris.withAppendedId(CONTENT_URI, id);
        // VERY IMPORTANT!!
        // Notify and observers of the change in the data set
        getContext().getContentResolver().notifyChange(insertId, null);

        return insertId;
    }
    return null;
}

@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
    // TODO Auto-generated method stub
    return 0;
}

@Override
public int update(Uri uri, ContentValues values, String selection,
        String[] selectionArgs) {
    // TODO Auto-generated method stub
    return 0;
 }

 }

MainActivity.java

 package bcs421.jorgeramirez.hwk.gradeapp.adv;

 import android.app.Activity;
 import android.content.ContentResolver;
 import android.content.Intent;
 import android.database.Cursor;
 import android.net.Uri;
 import android.os.Bundle;
 import android.util.Log;
 import android.view.Menu;
 import android.view.MenuItem;
 import android.view.View;
 import android.view.View.OnClickListener;
 import android.widget.Button;
 import android.widget.TextView;

 public class MainActivity extends Activity {

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    String[] projection = { GradeDBOpenHelper.KEY_ID,
            GradeDBOpenHelper.COLUMN_CAT,
            GradeDBOpenHelper.COLUMN_NUM,
            GradeDBOpenHelper.COLUMN_DESC,
            GradeDBOpenHelper.COLUMN_GRD,
            GradeDBOpenHelper.COLUMN_DATE
    };

    // Get the content resolver
    ContentResolver cr = getContentResolver();

    // Query the ContentProvider using the ContentResolver
    Uri targetUri = Uri.parse("content://bcs421.jorgeramirez.hwk.gradeapp");
    Cursor resultCursor = cr.query(targetUri, projection, null, null, null);


    TextView numGrade = (TextView)findViewById(R.id.numGrade);
    String numG = Double.toString(calcAverage(resultCursor));
    numGrade.setText(numG);

    Button viewGradeList = (Button)findViewById(R.id.viewGradesButton);
    viewGradeList.setOnClickListener(new OnClickListener(){

        @Override
        public void onClick(View v) {
            Intent intent = new Intent(MainActivity.this, GradeListActivity.class);
            startActivity(intent);

        }

    });




}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
    // Inflate the menu; this adds items to the action bar if it is present.
    getMenuInflater().inflate(R.menu.main, menu);
    return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
    // Handle action bar item clicks here. The action bar will
    // automatically handle clicks on the Home/Up button, so long
    // as you specify a parent activity in AndroidManifest.xml.
    int id = item.getItemId();
    if (id == R.id.action_settings) {
        return true;
    }
    return super.onOptionsItemSelected(item);
}

public double calcAverage (Cursor resCurs)
{
    int hwkSum = 0, examSum = 0, labSum = 0, quizSum = 0;
    int hwkCnt = 0, examCnt = 0, labCnt = 0, quizCnt = 0;
    double hwkAvg = 0, examAvg = 0, labAvg = 0, quizAvg = 0;
    double hwkPortion, examPortion, labPortion, quizPortion;
    double totAverage;

    int categoryIndex = resCurs.getColumnIndex(GradeDBOpenHelper.COLUMN_CAT);
    int gradeIndex = resCurs.getColumnIndex(GradeDBOpenHelper.COLUMN_GRD);
    String category;
    int gd;

    while (resCurs.moveToNext())
    {
        category = resCurs.getString(categoryIndex);
        gd = resCurs.getInt(gradeIndex);
        switch (category)
        {
        case "Homework":
            hwkSum = hwkSum + gd;
            ++hwkCnt;
            break;
        case "Exam":
            examSum = examSum + gd;
            ++examCnt;
            break;
        case "Lab":
            labSum = labSum + gd;
            ++labCnt;
            break;
        case "Quiz":
            quizSum = quizSum + gd;
            ++quizCnt;
            break;
        default:
            break;
        }
    }

    if (hwkCnt != 0)
    {
        hwkAvg = hwkSum/hwkCnt;
    }
    if(examCnt != 0)
    {
        examAvg = examSum/examCnt;
    }
    if(labCnt != 0)
    {
        labAvg = labSum/labCnt;
    }
    if(quizCnt != 0)
    {
        quizAvg = quizSum/quizCnt;
    }

    hwkPortion = hwkAvg * .35;
    examPortion = examAvg * .50;
    labPortion = labAvg * .05;
    quizPortion = quizAvg * .10;

    totAverage = hwkPortion + examPortion + labPortion + quizPortion;

    return totAverage;

 }
 }

PDO says it updates the table, but it actually doesn't

I'm trying to use a form to update a sql table by first getting its data (editrower.php) and setting that as values in the form, then using the form to update the table (update_contactrequest.php) but it returns saying the rower was updated yet the table does not update.

editrower.php

<?php
require('login.php');
?>
        <?php
        include 'php/mysql_connect.php';
        if(isset($_GET['id'])){
        $q = $db->prepare('SELECT * FROM rowercontacts WHERE id=:id LIMIT 1');
        $q->execute(array(':id'=>$_GET['id']));
        $row = $q->fetch(PDO::FETCH_ASSOC);
        if($row){
        echo '
      <form method="post" action="php/update_contactrequest.php"><div class="col-xs-9 col-md-6 col-lg-6">
        <div class="form-group">
          <input type="hidden" name="id" id="id" value="'.$_GET['id'].'">
          <label for="firstname">First Name</label>
          <input type"text" class="form-control" name="firstname" placeholder="First Name" value="'.$row['firstname'].'" />
        </div>
        <div class="form-group">
          <label for="lastname">Last Name</label>
          <input type="text" class="form-control" name="lastname" placeholder="Last Name" value="'.$row['lastname'].'" />
        </div>
        <div class="form-group">
          <label for="address">Home Address</label>
          <input type="text" class="form-control" name="address" placeholder="Address" value="'.$row['address'].'" />
        </div>
        <div class="form-group">
          <label for="city">City</label>
          <input type="text" class="form-control" name="city" placeholder="City" value="'.$row['city'].'" />
        </div>
        <div class="form-group">
          <label for="zip">Zip Code</label>
          <input type="text" class="form-control" name="zip" placeholder="Zip" value="'.$row['zip'].'" />
        </div>
        <div class="form-group">
          <label for="email">Email Address</label>
          <input type="email" class="form-control" name="email" placeholder="Your Email" value="'.$row['email'].'" />
        </div>
        <div class="form-group">
          <label for="cellphone">Cell Phone Number</label>
          <input type="text" class="form-control" name="cell" placeholder="Cell Number" value="'.$row['cell'].'" />
        </div>
        <div class="form-group">
          <label for="homephone">Home Phone Number</label>
          <input type="text" class="form-control" name="home" placeholder="Home Phone Number" value="'.$row['home'].'"/>
        </div>
        <br><br>
        <div>        
          <h3>Parent/Guardian Information</h3>
          <br>
        </div>
        <h4>Parent/Guardian 1</h4>
        <div class="form-group">
          <label>Name</label>
          <input type="text" class="form-control" name="p1name" placeholder="Guardian 1 Name" value="'.$row['p1name'].'" />
        </div>
        <div class="form-group">
          <label>Email Address</label>
          <input type="email" class="form-control" name="p1email" placeholder="Guardian 1 Email" value="'.$row['p1email'].'" />
        </div>
        <div class="form-group">
          <label>Phone</label>
          <input type="text" class="form-control" name="p1phone" placeholder="Guardian 1 Phone" value="'.$row['p1phone'].'" />
        </div>
        <div class="form-group">
          <label>Occupation</label>
          <input type="text" class="form-control" name="p1occupation" placeholder="Guardian 1 Occupation" value="'.$row['p1occupation'].'" />
        </div>
        <div class="form-group">
          <label>Relation</label>
          <input type="text" class="form-control" name="p1relation" placeholder="mother, father, etc." value="'.$row['p1relation'].'" />
        </div>
        <br>
        <h4>Parent/Guardian 2</h4>
        <div class="form-group">
          <label>Name</label>
          <input type="text" class="form-control" name="p2name" placeholder="Guardian 2 Name" value="'.$row['p2name'].'" />
        </div>
        <div class="form-group">
          <label>Email Address</label>
          <input type="email" class="form-control" name="p2email" placeholder="Guardian 2 Email" value="'.$row['p2email'].'" />
        </div>
        <div class="form-group">
          <label>Phone</label>
          <input type="text" class="form-control" name="p2phone" placeholder="Guardian 2 Phone" value="'.$row['p2phone'].'" />
        </div>
        <div class="form-group">
          <label>Occupation</label>
          <input type="text" class="form-control" name="p2occupation" placeholder="Guardian 2 Occupation" value="'.$row['p2occupation'].'" />
        </div>
        <div class="form-group">
          <label>Relation</label>
          <input type="text" class="form-control" name="p2relation" placeholder="mother, father, etc." value="'.$row['p2relation'].'" />
        </div>
        <br>
        <div class="form-group">
          <textarea class="form-control" rows="3" name="extrainformation" placeholder="Additional Info (parent work phone numbers, health information, etc.)">'.$row['extrainformation'].'</textarea>
        </div>
        <br>
        <small>Any additional parents/guardians who wish to be notified of updates can request under the "contact" section</small>
        <br><br>
        <input type="submit" class="btn btn-default" value="Update" />
      </div></form>
        ';
      }
      else{
      echo 'No rower found';
      }
      }
      else{
        echo 'No rower found';
      }
      ?>

update_contactrequest.php:

<?php
session_start();
if($_SESSION['loggedIn'] == true){

$rower_id= $_POST['id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$city = $_POST['city'];
$zip = $_POST['zip'];
$email = $_POST['email'];
$cell = $_POST['cell'];
$home = $_POST['home'];
$p1name = $_POST['p1name'];
$p1email = $_POST['p1email'];
$p1phone = $_POST['p1phone'];
$p1occupation = $_POST['p1occupation'];
$p1relation = $_POST['p1relation'];
$p2name = $_POST['p2name'];
$p2email = $_POST['p2email'];
$p2phone = $_POST['p2phone'];
$p2occupation = $_POST['p2occupation'];
$p2relation = $_POST['p2relation'];
$extrainformation = $_POST['extrainformation'];
  // connection to the database
try {
$bdd = new PDO('mysql:host=localhost;dbname=home','username','password');
} catch(Exception $e) {
exit('Unable to connect to database.');
}

// insert the records
$sql = "UPDATE rowercontacts SET firstname=:firstname, lastname=:lastname, address=:address, city=:city, zip=:zip, email=:email, cell=:cell, home=:home, p1name=:p1name, p1email=:p1email, p1phone=:p1phone, p1occupation=:p1occupation, p1relation=:p1relation, p2name=:p2name, p2email=:p2email, p2phone=:p2phone, p2occupation=:p2occupation, p2relation=:p2relation, extrainformation=:extrainformation WHERE id=:rower_id";
$q = $bdd->prepare($sql);
if($q->execute(array(':firstname'=>$firstname, ':lastname'=>$lastname, ':address'=>$address, ':city'=>$city, ':zip'=>$zip, ':email'=>$email, ':cell'=>$cell, ':home'=>$home, ':p1name'=>$p1name, ':p1email'=>$p1email, ':p1phone'=>$p1phone, ':p1occupation'=>$p1occupation, ':p1relation'=>$p1relation, ':p2name'=>$p2name, ':p2email'=>$p2email, ':p2phone'=>$p2phone, ':p2occupation'=>$p2occupation, ':p2relation'=>$p2relation, ':extrainformation'=>$extrainformation, ':rower_id'=>$id))){
  echo '<script type="text/javascript">alert("Rower Updated.");location.href="../rowerlist.php";</script>';
}
else{
  echo '<script type="text/javascript">alert("Something went wrong...");location.href="../rowerlist.php";</script>';
}

}
?>

Select ___where __LIKE ___

I want to select all the tables which don't contain the character _ from a database, for this I used the request :

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema="DB_NAME"
AND TABLE_NAME NOT LIKE '%_%'; 

But it doesn't seem to work.

Where to put using(SqlConnection)

I am creating a framework to add plugins to an application of mine. Each plugin must implement an abstact class. Each plugin is then compiled as a DLL which the main application can find with Directory.GetFiles(myPath, "*.dll")

This all works swimmingly and I can instantiate my plugins in the main application and use them. Each plugin is basically a dashboard widget that the user can add to his dashboard to show him some graphs or charts. This means, each plugin has a timer and on every timer event it refreshes the graph with data from the application's SQL database.

So my question is, where do I put the SqlConnection? Do I create one SqlConnection and pass it as a parameter to each plugin or do I pass the connection string and have each plugin create its own SqlConnection?

If I pass the application's SqlConnection to the plugin then I'd imagine it would involve some managing of the connection inside the plugin. I'd obviously have to check that it's open and what do I do if it's state is ConnectionState.Fetching or ConnectionState.Executing? It just seems unwieldy.

But on the other hand, considering that multiple users will be running the application and each user might have multiple plugins selected in his dashboard, it could add up to a number of SqlConnections. Is that desirable? Should I consider a third option where the plugin gives its query to the host which queues it up with other queries from other plugins and return the result set to the plugin once the query has executed? That way at least there's only one SqlConnection for every user, regardless of how many plugins they've selected.

To be honest, this last option seems rather complicated to me and I'm not quite sure yet how I'd implement that. If anyone could point me towards an article that explains something similar, I'd really appreciate it.

SQL/HQL Count Total Not Working on Join

I'm creating a recommender using some simple probability formulas. I'm working with the Million Song Dataset large dataset (189M rows and 3 columns) with my Hadoop cluster, using Hive. Here is a sample of the initial dataset:

million_song - initial dataset

 user   song    play_count
 c3fb2  SOXOQ   18
 c3fb2  SOZVC   1
 6041e  SOBHN   3
 6041e  SOBXT   5

But first I created a table from the initial dataset, which a sample is show here:

million_both - adds up occurrence of two songs listened by same user

 driver_song_id     also_song_id    play_count
 SOXOQ              SOBXT           1642
 SOBHN              SOBXT           2168
 SOBXT              SOZVC           1742

I'm trying to add these two tables above together, and the issue is I can't get the correct count_n_users, which should be the same # for each row in the table. Here is my code:

 select a.driver_song_id, a.also_song_id, a.play_count, 
 count(distinct b.user), sum(distinct b.user) as count_n_users 
 from (select driver_song_id, also_song_id, play_count from million_both) as a 
 join (select user, song, play_count from expp_team.million_song) as b 
 on a.driver_song_id = b.song
 group by a.driver_song_id, a.also_song_id, a.play_count;

The issue I cannot get the correct count_n_users, as of right now it's overstating the value.

This is what I'd like the output to look like:

 driver_song_id     also_song_id    play_count  c(b.user)  count_n_users
 SOXOQ              SOBXT           1643        463         139,738,054           
 SOBHN              SOBXT           2168        483         139,738,054
 SOBXT              SOZVC           1742        725         139,738,054

How to remove duplicates in a complicated JOIN

I've an EMPLOYEE table which has built-in hierarchy structure as well (using manager column)

I've another REGION table which represents manager-region relation

I'm trying to create a SQL which will show which employees fall under which region by following up the hierarchy chain.

Constraints / Rules:

• It's possible that employee's immediate manager may not have a region - so I need to continue up the chain.

• I have guarantee that 4 level up the chain someone will have a region.

• If region is found before the 4th level then use lower manager's region

This is the naive SQL I came up with (but results have duplicates - 3rd Rule failed)

select distinct e.name, r.region
from employee e 
left outer join employee mgr1 on mgr1.id = e.manager 
left outer join employee mgr2 on mgr2.id = mgr1.manager 
left outer join employee mgr3 on mgr3.id = mgr2.manager 
left outer join employee mgr4 on mgr4.id = mgr3.manager 
left outer join REGION r on 
      (  r.id = mgr1.id 
      or r.id = mgr2.id 
      or r.id = mgr3.id 
      or r.id = mgr4.id  ) 

where e.IS_MANAGER = 'N'; //only interested in users for now; assume a flag

And this is the ResultSet:

How do I conditionally stop the left outer join if I already found a region?

Update statement to set a column based the maximum row of another table

I have a Family table:

SELECT * FROM Family;

id | Surname  | Oldest
---+----------+------
 1 | Byre     | NULL
 2 | Summers  | NULL
 3 | White    | NULL
 4 | Anders   | NULL

The Family.Oldest column is not yet populated. There is another table of Children:

SELECT * FROM Children;

id | Name     | Age  | Family_FK
---+----------+------+--------
 1 | Jake     | 8    | 1
 2 | Martin   | 7    | 2
 3 | Sarah    | 10   | 1
 4 | Tracy    | 12   | 3

where many children (or no children) can be associated with one family. I would like to populate the Oldest column using an UPDATE ... SET ... statement that sets it to the Name of the oldest child in each family. Finding the name of each oldest child is a problem that is solved quite well here: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

However, I don't know how to use the result of this in an UPDATE statement to update the column of an associated table using the h2 database.

print departments hierarchy to a table

I have a relationship table for departments as below:

+---------------+----------------+
|     Dept.     | superior Dept. |
+---------------+----------------+
| "00-01"       | "00"           |
| "00-02"       | "00"           |
| "00-01-01"    | "00-01"        |
| "00-01-02"    | "00-01"        |
| "00-01-03"    | "00-01"        |
| "00-02-01"    | "00-02"        |
| "00-02-03"    | "00-02"        |
| "00-02-03-01" | "00-02-03"     |
+---------------+----------------+

Now I want to list them according to their tiers like this:

+-----------+--------------+--------------+--------------+
| Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. |
+-----------+--------------+--------------+--------------+
|        00 |              |              |              |
|           | 00-01        |              |              |
|           |              | 00-01-01     |              |
|           |              | 00-01-02     |              |
|           | 00-02        |              |              |
|           |              | 00-02-01     |              |
|           |              | 00-02-03     |              |
|           |              |              | 00-02-03-01  |
+-----------+--------------+--------------+--------------+

I can construct the relationship tree using the code below:
TreeNode.java

import java.util.LinkedList;
import java.util.List;

public class TreeNode {
  public String value;
  public List children = new LinkedList();

  public TreeNode(String rootValue) {
    value = rootValue;
  }

}

PairsToTree.java

import java.util.*;

public class PairsToTree {

  public static void main(String[] args) throws Exception {
    // Create the child to parent hash map
    Map <String, String> childParentMap = new HashMap<String, String>(8);
    childParentMap.put("00-01", "00");
    childParentMap.put("00-02", "00");
    childParentMap.put("00-01-01", "00-01");
    childParentMap.put("00-01-02", "00-01");
    childParentMap.put("00-01-03", "00-01");
    childParentMap.put("00-02-01", "00-02");
    childParentMap.put("00-02-03", "00-02");
    childParentMap.put("00-02-03-01", "00-02-03");

    // All children in the tree
    Collection<String> children = childParentMap.keySet();

    // All parents in the tree
    Collection<String> values = childParentMap.values();

    // Using extra space here as any changes made to values will
    // directly affect the map
    Collection<String> clonedValues = new HashSet();
    for (String value : values) {
      clonedValues.add(value);
    }

    // Find parent which is not a child to any node. It is the
    // root node
    clonedValues.removeAll(children);

    // Some error handling
    if (clonedValues.size() != 1) {
      throw new Exception("More than one root found or no roots found");
    }

    String rootValue = clonedValues.iterator().next();
    TreeNode root = new TreeNode(rootValue);

    HashMap<String, TreeNode> valueNodeMap = new HashMap();
    // Put the root node into value map as it will not be present
    // in the list of children
    valueNodeMap.put(root.value, root);

    // Populate all children into valueNode map
    for (String child : children) {
      TreeNode valueNode = new TreeNode(child);
      valueNodeMap.put(child, valueNode);
    }

    // Now the map contains all nodes in the tree. Iterate through
    // all the children and
    // associate them with their parents
    for (String child : children) {
      TreeNode childNode = valueNodeMap.get(child);
      String parent = childParentMap.get(child);
      TreeNode parentNode = valueNodeMap.get(parent);
      parentNode.children.add(childNode);
    }

    // Traverse tree in level order to see the output. Pretty
    // printing the tree would be very
    // long to fit here.
    Queue q1 = new ArrayDeque();
    Queue q2 = new ArrayDeque();
    q1.add(root);
    Queue<TreeNode> toEmpty = null;
    Queue toFill = null;
    while (true) {
      if (false == q1.isEmpty()) {
        toEmpty = q1;
        toFill = q2;
      } else if (false == q2.isEmpty()) {
        toEmpty = q2;
        toFill = q1;
      } else {
        break;
      }
      while (false == toEmpty.isEmpty()) {
        TreeNode node = toEmpty.poll();
        System.out.print(node.value + ", ");
        toFill.addAll(node.children);
      }
      System.out.println("");
    }
  }

}

but can't figure it out how to format the output to resemble a table. Or is there a sql statement/stored procedure(like this question) to do this?

EDIT: the Dept. name is just a sample for the sake of convenience, it can be arbitrary string.

How to specify sort order on multiple self joins

I've an attribute table like

CREATE TABLE attributes (
    attribute_id INT,
    product_id INT,
    random INT,
    UNIQUE KEY (attribute_id,random,product_id),
    KEY (product_id)
);

random is a random integer number calculated on insert for shuffling products (that's O.K. for my needs). There are self-join queries like

SELECT DISTINCT x.product_id
FROM attibutes x
INNER JOIN attributes y ON x.product_id=y.product_id
INNER JOIN attributes z ON x.product_id=z.product_id
WHERE x.attribute_id IN (20000085,20000090) AND
    y.attribute_id IN (10000007) AND
    z.attribute_id IN (30000050,30000040,30000012)
LIMIT 0,100;

As you can see I want to select products which have at least one attribue in each number range. MySQL is so clever to choose table alias for first query itself, depending on selectivity of UNIQUE key. As expected the result is sorted in order of column random because of UNIQUE key. But how can I advise MySQL to revert the order? When adding ORDER BY x.random DESC it could happen that MySQL uses filesort for ordering because if it uses table alias y for base query (because of better selectivity of attribute ID 10000007) it has to use UNIQUE key of alias x. The problem is: I don't know which alias MySQL does use (it's decided by its query optimizer). So how to specify order direction?

(I want to note that table contains about 60 million rows, so usage of filesort or not would be significant in response time)

Get Remain of Account in Accounting software

I have a problem

SELECT Tbl_ZAsnad.zs_id ,
       V_ALLAccounts.h_name ,
       zs_bed ,
       zs_bes ,
       (SELECT SUM(Tbl_ZAsnad.zs_bed) - SUM(Tbl_ZAsnad.zs_bes) FROM Tbl_ZAsnad
        WHERE k_code = 12 and m_code = 2 and t_code = 10 and zsid < "***GET Current value of zs_id***")
FROM Tbl_ZAsnad , V_ALLAccounts
WHERE k_code= kid
and m_code = mid 
and t_code = tid
and k_code = 12 and m_code = 2 and t_code = 10  

I want to get current value of zs_id

Using a SQL function to pad strings with spaces

Is it inefficient to use a user defined function to pad spaces? I have a padding function that I'd more intuitive than using the built in REPLICATE function but I am afraid it is introducing inefficiency into the code.

The padding must be done in SQL.

Display info from a xsql page inside of my HTML page

I have my xSQL, XSL, and HTML functioning properly.

http://ift.tt/1aWveBf

I can go here and search for the player first name "Lucas" and last name "Gass". I am whisked away to the xSQL page and the info displays properly. I would like to be able to have this information generate within the context of my players.html page. So the entire site continues to have the look and feel of my HTML.

What is the best way to go about this?

Histogram: Counting orders with variable bins in SQL

I have a table containing orders, items, and prices. I am trying to generate histograms for each item based on the prices.

Create Table #Customer_Pricing
(
customer_id int,
item_id VARCHAR(10),
qty DECIMAL(5,2),
price DECIMAL(5,2),
)
;
GO

-- Insert Statements
Insert into #Customer_Pricing values(128456, 'SOM 555', 8, 2.50)
Insert into #Customer_Pricing values(123856, 'SOM 554', 1, 2.50)
Insert into #Customer_Pricing values(123456, 'SOM 554', 55, 2.00)
Insert into #Customer_Pricing values(123556, 'SOM 555', 2, 2.20)
Insert into #Customer_Pricing values(123456, 'SOM 553', 12, 2.13)
;

For each item, I wanted 3 bins so I determined the bin sizes by dividing the difference of the MAX-MIN by 3, then adding that value to the MIN.

WITH Stats_Table_CTE (item_id2,max_p, min_p, int_p, r1_upper, r2_lower, r2_upper, r3_lower)
AS
(   SELECT  item_id
            ,max(price) 
            ,min(price)
            ,(max(price) - min(price))/3
            ,min(price)+(max(price) - min(price))/3-0.01
            ,min(price)+(max(price) - min(price))/3         
            ,min(price)+((max(price) - min(price))/3)*2-0.01
            ,min(price)+((max(price) - min(price))/3)*2                                             
        FROM #Customer_Pricing
        GROUP BY item_id)

Now, I need to count the frequencies for each range and each item. I have attempted to do so by using SUM(CASE...) but was unsuccessful.

SELECT item_id
    ,SUM(CASE WHEN price <= r1_upper, THEN 1 ELSE 0 END) AS r1_count
    ,SUM(CASE WHEN price >= r2_lower AND <= r2_upper, THEN 1 ELSE 0 END) AS r2_count
    ,SUM(CASE WHEN price >= r3_lower, THEN 1 ELSE 0 END) AS r3_count
FROM Stats_Table_CTE
GROUP BY item_id

I also attempted to use COUNT in the form SELECT item_id, price count(price <= r1_upper) AS r1_count.... but I got stuck

In one attempt, INNER JOINed the #Customer_Pricing table and Stats_Table_CTE but didn't know where to go from there.

Ideally, I would like the output table to appear as follows: *This is not the actual data, but I included it to show the desired format of the output. Item ID min_p r1_upper (r2 bins) r3_lower max_p r1_count r2_ct SOM 553 2.00 2.16 saving space 2.33 2.50 2 1 SOM 554 2.13 2.48 2.88 3.25 1 0 SOM 555 2.31 2.51 2.72 2.92 3 2

*The format of the output table is off, but I have item ID, the bins, and the counts across the top grouped by item

oracle sql to get min timestamp when the count of results large than a number

in order to improve the performance, I need a sql to implement the following requirement.

If there is a table and has the following column:

id timestamp value

How can I get the min timestamp when the count of the result > 100000 ?

for example if the timestamp is :t1, then the following sql result--count(*) will > 100000

select count(*) from table where  timestamp < :t1

Group in rows and column

I am sort of new to sql and I have a question that I havent been able to resolve, I have a view that combines info from different tables doing different operations and basically, simplified, it looks a bit like this:

Risk_Status    Task_Name     Completion 
---------------------------------------
Green              Task1        On Time 
Yellow             Task2        Delayed 
Yellow             Task3        On Time 
Green              Task4        Delayed
Red                Task5        Delayed
Yellow             Task5        Delayed
Red                Task5        On Time
Red                Task5        Delayed

What I want to accomplish is to group all tasks by status, following the count of tasks that fall on each status AND have a column that list all delayed or on time tasks that fall in the different statuses as well:

Status      Number of Tasks    Number of Delayed Tasks
-------------------------------------------------------
Green                     2                          1
Yellow                    3                          2
Red                       3                          2

I have tried duplicating the view in the from clause and making it select only delayed tasks on the where class but it's not giving me accurate results, I'm not sure what else to try.

Any help is appreciated :)

Get values set in SQL

I have this kind of table, on an SQLite database :

Is there a way to get all the different values for the username column, ie getting - in this example - user1 user2 user3 without others informations ?

Thanks.

Users in my social network can't register and login

I made a simple social network system, with register and login function, but whem the user register and try to login, he give this message:

Fatal error: Call to a member function rowcount() on a non-object in home/login.php on line 7

There is some problem in my login.php code?:

<?php
include('dbcon.php');
$username = $_POST['username'];
$password = $_POST['password'];

$query = $conn->query("select * from database where username = '$username' and password = '$password'");
$count = $query->rowcount();
$row = $query->fetch();
if ($count > 0){
session_start();
$_SESSION['id'] = $row['member_id'];
 header('location:home.php'); 
}else{
 header('location:index.php'); 
}
?>

"SQL command not properly ended" when using subquery

I have the following query working:

select a.column_value as artigo from encomenda e, table(e.artigos) a where e.id = 2;

This query returns the following output (one row of type menu_t and another of type bebida_menu_t).

From that result, I want to extract only the menu_t rows. So, I've tried the following query:

select * from (select a.column_value as artigo from encomenda e, table(e.artigos) a where e.id = 2) as subquery where subquery.artigo is of (menu_t); 

Which gives me the error 00933. 00000 - "SQL command not properly ended" and I can't understand why.

C# with SQL Server

How I can remove data from dataGridView1 after transfer to another dataGridVew by button???

Multiple ORDER BY (get latest element) MySQL

I have the following tables : Radios, Podcasts and Shows. A radio has many podcasts, and a podcasts has many shows. Each podcast can be ordered by its history, and each show can be ordered by its publication_date.

I want to get all the podcasts associated with their latest show.

The query looks like that :

SELECT r.name AS radio_name,Pod.*,Sh.* 
FROM podcasts Pod 
  INNER JOIN radios r ON (r.id=Pod.radio_id) 
  INNER JOIN shows Sh ON (Sh.podcast_id=Pod.id) 
ORDER BY Pod.history LIMIT 5

I'd like to have a second ORDER BY Sh.publication_date but I don't really know where it should be.

Display count of unique values in MySQL db table using PHP

So I have a table named pins that has 3 columns that need to be taken into consideration. These columns are plan and order_id.

I need to get a count for all of the pins that have an order_id=0 and plan=9.

This is what I have so far:

$qT="SELECT plan, COUNT(*) as cnt FROM pins WHERE order_id=0 and plan=9";
$res=mysql_query($qT);<br/>
mysql_free_result($res);

while($row = mysql_fetch_array($res)) {<br/>
echo $row['plan'];<br/>
}

Any help in displaying the results would be a great help.

how to make a multi delete query

Hello people i have a question about this query;

DELETE FROM 
 our_work, 
 our_work_images, 
 our_work_portf_img
WHERE 
 our_work.id = 6
AND 
 our_work_portf_img.portf_id = 6
AND
 our_work_portf_img.img_id = our_work_images.img_id;

i want to delete all relations from the first table "our_work" my pair table is named "our_wrok_porft_img" and my image table "our_work_images"

how to obtain the following output from a single table

Table Name: Employee
S_No    Heading  Summary
 1       a         A
 2       b         B
 3       c         C
 4       d         D

I want to display it as:

S_No  Heading  Summary   S_No1  Heading  Summary2
1       a        A         2       b         B
3       c        C         4       d         D

Select mysql data starting from a specific date

I have player statistics which I would like to publish from certain dates. At the moment I can see statistics in the database from the begining.

SELECT name, 
       bankmoney AS Bank,
       Playerkills AS 'Player Kills',
       deathcount AS Deaths ,
       aikills AS 'AI Kills',
       teamkills AS 'Team Kills',
       revivecount AS Revives ,
       capturecount AS 'Territories Captured',
       LastModified AS 'Last Seen'
FROM playerinfo
JOIN playerstats
ON playerinfo.UID = playerstats.PlayerUID
ORDER BY BankMoney DESC;

But I would like to present statistics from the start of the day and the start of the week.

How would I do that ?

How to change the default port of mysql from 3306 to 3360

I want to change the default port number of mysql server presently it is 3306. I want to change it to 3360 I have tried

-- port=3360

But things are not working for me. Please provide query to change port not any configuration. I am using windows 8 -64 bi. I know this is not right forum to ask this question. but please don't downvote