0
votes
1answer
24 views

Is there any performance difference between a group by statement and nested select statements in sql?

I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following: tblPurchase ...
1
vote
1answer
27 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
0
votes
1answer
38 views

How to query group for increase or decrease in value

In MySQl I have a table positions and I would like to find out if position of certain app for certain feed in a certain country type has been increasing/decreasing and by how much from the last ...
3
votes
1answer
66 views

Query with dynamic ranges in PostgreSQL based on status

We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have ...
0
votes
2answers
92 views

PostgreSQL : select columns inside json_agg

If I have a query like SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
0
votes
1answer
54 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I ...
0
votes
2answers
45 views

“GROUP BY” in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | ...
18
votes
6answers
677 views

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
2
votes
1answer
69 views

Select query - group by and sub group by

CREATE TABLE [dbo].[temptable] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [uid] INT NOT NULL, [pid] INT NOT NULL, [event] INT NOT NULL, [timeStamp] DateTime NOT NULL ) I want ...
0
votes
2answers
106 views

What is the difference between COUNT(*) AND COUNT([attribute]) in the following scenario?

I have the following tables : DEPARTMENT department_id department_name manager_id location_id and EMPLOYEES employee_id first_name last_name email phone_number hire_date job_id salary ...
0
votes
0answers
31 views

How can I number my groups using an Access “Find Duplicates” Query?

I have a query created by the MS Access 2010 find duplicates wizard. It is: SELECT * FROM fbExpenses WHERE [expense_date] In (SELECT [expense_date] FROM [fbExpenses] As ...
0
votes
3answers
120 views

How to query for 'First showed up as'

Given the following table: Date Trd (INT(10)) Code (VARCHAR(10)) T-Date 1-Jan-14 1 B 1-Nov-13 1-Jan-14 2 B 3-Oct-13 ...
0
votes
1answer
73 views

How to remove duplicates in col1 and get group MAX in col2? / mysql

Given the following table: col1 Col2 A 1 B 1 C 1 B 2 B 3 A 0 C 5 how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the ...
3
votes
4answers
981 views

Select rows based on latest date with multiple joins

I have this query (SQLFiddle): SELECT c.name, a.user_id, a.status_id, a.title, a.rtime, u.user_name, s.status_name FROM company c LEFT JOIN action a ON a.company_id=c.id LEFT JOIN user u ON ...
0
votes
1answer
228 views

Get max date for each year from list of dates

Need to get max date for each year from list of arbitrary dates: date year ================= 1/10/2013 2013 3/26/2013 2013 4/20/2012 2012 9/10/2012 2012 5/6/2011 2011 So ...
3
votes
1answer
633 views

Can column name be “Group” in Postgresql or in any databases

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax ...
3
votes
2answers
172 views

How do I use window functions (“over”) together with crosstab in Postgres?

Table: application_app applied_date | applied_class | id -------------------------------+---------------+----- 2013-03-06 15:59:49.22774-05 | BOSFAL13 | 13 2013-05-12 ...
1
vote
0answers
80 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
0
votes
1answer
42 views

SELECT to only see Offsetting Transactions in table?

I have a temp table like this I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos. ...
-3
votes
1answer
6k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
2
votes
1answer
265 views

Grouping over multiple data dimensions (pivot)

I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise. The two dimensions ...
0
votes
1answer
182 views

Need to find the number of employee who are not in all department

i have a table T1 which has two columns empname , department every emp must be in 37 department , but there are some employee who are in less than 37 department . I need to find the names of emp who ...
2
votes
1answer
389 views

Is it possible to reduce a group to a row based on some criteria?

I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows ...
-1
votes
2answers
2k views

How to use GROUP BY after the Having clause Mysql

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions All the accounts which is not ...
1
vote
1answer
103 views

GROUP BY with OR/AND clause?

Lets say my database looks like this: Table: messages +----+------+----+----------+----------+ | id | from | to | double_1 | double_2 | +----+------+----+----------+----------+ | 1 | 1 ...
8
votes
6answers
2k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
5
votes
2answers
499 views

T-SQL Purpose of MAX in this Group By Query

I have run into some pre-existing SQL that I'm having a hard time uderstanding. SELECT MAX(I.Symbol) Symbol , MAX(I.Ticker) CUSIP , MAX(I.Name) Name , SUM(H.Quantity) TotalQuantity , ...
2
votes
1answer
5k views

Multiple “GROUP_CONCAT”s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
4
votes
2answers
331 views

MySQL check duplicate with group by using wildcard?

+----+--------------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 1 | Ramesh Olive | 32 | ...
2
votes
2answers
4k views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
4
votes
1answer
122 views

GROUP BY on a dependent field

Let's say I have a SELECT statement as follows: SELECT ..., field1 FROM ... GROUP BY field1 Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent ...
0
votes
2answers
88 views

Grouping data checking against top values

I've got data in this form: CallNum Value Accepted -------------------------------------- 971374 81.482204444473609 True 971374 83.783551111089764 False 971374 97.936875555547886 ...