Extracting Several Math Operations Outputs from Single Select Query

Extracting multiple choice answers stored in single database field as integer

Here is an example of using bitwise operators to extract what you want:

Then it's a matter of pivoting and processing that into what you require.

SELECT Customer, answer, 
answer & POWER(2,0) pos1,
answer & POWER(2,1) pos2,
answer & POWER(2,2) pos3,
answer & POWER(2,3) pos4,
answer & POWER(2,4) pos5,
answer & POWER(2,5) pos6,
answer & POWER(2,6) pos7
from (
SELECT 1 Customer, 6 answer
union all
SELECT 1 Customer, 2 answer
union all
SELECT 1 Customer, 62 answer
) F

Do Math on Two Scalar Queries in SQL

Just put a SELECT in front:

SELECT (SELECT sum(depamt) FROM Daily1) - (SELECT sum(depamt) FROM Daily2);

I prefer putting subqueries in the FROM clause:

SELECT sum1 - sum2
FROM (SELECT sum(depamt) as sum1 FROM Daily1) x CROSS JOIN
     (SELECT sum(depamt) as sum2 FROM Daily2) y;

This allows you to choose each value individually, if you like.

Find students studying two subjects with three different roles SQL

  Select student , subject 
  From students 
  where student in (
    Select student 
    From students 
    where num_roles = 3
    Group by student having count(subject) >1
  )
  and num_roles = 3

How to apply arithmetic operations on size of two queries

You can do this by using conditional analytic COUNTs and then filtering the rows accordingly, like so:

SELECT <list of t1 cols>
FROM   (SELECT t1.*, -- ideally, you should specify the exact columns you want to retrieve.
               COUNT(*) OVER () cnt_all,
               COUNT(CASE WHEN condition2 AND condition3 THEN 1 END) OVER () cnt_restricted
        FROM   t1
        WHERE  condition1)
WHERE  condition2
AND    condition3
AND    cnt_restricted/cnt_all > 0.15;

The OVER () part of the COUNT function is what makes it analytic - i.e. it won't collapse the rows. OVER () means "take the whole data set as one group, with no ordering and apply the COUNT function to it."

This has the advantage over Zip's answer, in that you are only querying the table once.

extract numbers from a *varchar* cell and do math with them in pure mysql

In case someone is looking for similar stuff, the complete answer is something like:

UPDATE `table`
SET cell = (
CAST( SUBSTRING( cell, 1, 2 ) AS UNSIGNED )
*
CAST( SUBSTRING( cell, 4, 2 ) AS UNSIGNED ) 
-
(SUBSTRING( latitud, 7, 1 ) = 'b')  
)
WHERE 1;   

Finding all instances where a foreign key appears multiple times grouped by month

You can do this with two levels of aggregation: first by month, course and student (while filtering on students having more than 4 submissions), then by month (while pivoting the dataset):

select
    month_submitted,
    count(*) filter(where course = 'courseA') active_students_in_courseA,
    count(*) filter(where course = 'courseB') active_students_in_courseB,
    count(*) total
from (
    select 
        date_trunc('month', date_submitted) month_submitted,
        course,
        student_id,
        count(*) no_submissions
    from submission
    where course in ('courseA', 'courseB')
    group by 1, 2, 3
    having count(*) > 4
) t
group by 1

SQL: Querying within multiple records that represent the same individuals

This will find all students who never were in a state school.

SELECT DISTINCT StudentID FROM alumni
WHERE StudentID NOT IN (
    SELECT StudentID FROM alumni WHERE State_Fee = 'state'
)

Select most popular and active values from an PostgresQL query

What you need here is a group by operator.

You want to regroup the students when they are in the same class, and then count the number of students in each class.

CREATE VIEW popularclasses AS
SELECT
  classes.classid,
  COUNT(*) AS amount
FROM
  classes
JOIN classmembers ON classes.classid = classmembers.classid
GROUP BY
  classes.classid
ORDER BY amount DESC LIMIT 10;

(I have not tried this code)

---

Related Topics