GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

1
vote
1answer
26 views

right join leaving one row from right table

I am using MariaDB,in my sample database I am having two tables department and emp. I want to display all the department having less than 50 employees. In this case it should display all the ...
3
votes
1answer
167 views

How to fetch data that is not similar

I have this table: | id | user_id | status | | a | 1 | p | | a | 1 | p | | a | 1 | np | | a | 1 | pp | | b | 2 | np | | b | 2 | np |...
0
votes
1answer
30 views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with ...
6
votes
1answer
137 views

Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

I'm finding that MySQL seems to have an issue when ONLY_FULL_GROUP_BY mode is enabled and the query includes a HAVING that refers to grouped columns from different tables that have the same name. ...
0
votes
1answer
13 views

Ratio per Group in PostgreSQL

If I have rows like this: lec1 major1 class1 10 lec1 major2 class1 40 lec1 major1 class2 30 lec1 major3 class3 35 lec2 major1 class3 15 lec2 major3 class3 10 And I need it to combine the ratio into:...
-3
votes
1answer
28 views

Postgresql Group by [closed]

I created a table in postgresQL like Now I ran a query like:: SELECT name FROM emp.company GROUP BY name And I am getting output like I am unable to get it whats exactly happening over here.
1
vote
2answers
40 views

Correct way to write this view? Total sales/purchases/profits by day

I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables: transaction: idtransaction (pk) user_iduser (fk) ...
-1
votes
1answer
25 views

in postgres how to select items that have a dupe across two columns

My data looks like this: queryable_type queryable_id User 1 User 2 User 2 I'd like to return dupes so that my result would look like this: queryable_type ...
0
votes
1answer
57 views

Is there a way to used grouped data multiple times in a query

Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments. The solution that I ...
4
votes
1answer
70 views

Pull more records based on IDs in comma separated list returned by GROUP_CONCAT [duplicate]

Is it possible to pull more records based on IDs in comma separated list return by GROUP_CONCAT? Is a LEFT JOIN possible here? If so, how can it be done. I am constantly failing at figuring this out, ...
2
votes
2answers
36 views

List all articles with their amounts at a certain date+time

I've got a table with articles, and their stock amount changes: TABLE stock_history: +------------+---------------------+--------+ | article_id | amount_at | amount | +------------+---------...
0
votes
1answer
72 views

how can we merge sum and count? [closed]

My result of the request of group by is : ref state quantity total PO3253 assinged 300.0 323400 PO3253 done 300.0 323400 When I delete state from ...
2
votes
1answer
207 views

How to get number of active users with one SQL-Query?

I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month. ...
6
votes
2answers
165 views

MySQL group by like statement?

I need to get some stats out of our MySQL-DB and wonder if there is a faster way in doing this. Basically I need info about user registration for any given month before including seperation by ...
4
votes
1answer
84 views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
1
vote
1answer
58 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
0
votes
0answers
30 views

Grouping and Transposing Rows to Columns in PostgreSQL

I have the follwing situation: I have profiles and tasks. One profile can be used once for each task. I track usage in profiles_used table. I need to build a report that shows how many profiles of ...
1
vote
1answer
65 views

Unknown column 'actions_in_group' in 'field list'

I keep getting: Unknown column 'actions_in_group' in 'field list' Here's the SQLFiddle: http://sqlfiddle.com/#!9/4ddf0/19 SELECT *, SUM(actions_in_group) AS total_rows_in_group, GROUP_CONCAT(...
0
votes
1answer
28 views

Optimising a query with two aggregate fields

I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results. For every combination of typeId ...
0
votes
1answer
33 views

SQL query help: Calculating days between observations

I'm extremely new to SQL, in fact, I just really started playing around with it. So I'm not exactly sure how to search for appropriate terms related to the question I have. I'll do my best. CONTEXT:...
1
vote
1answer
145 views

Improving performance for sum and group by with large datasets

I have a query that looked like this: SELECT sum(score) as score, count(distinct(user_id)) number, round(sum(score)/count(distinct(user_id))) score_per_member, teams.*, types.name as type FROM `...
0
votes
1answer
26 views

Fetch column data against group by data

The source table structure is as defined in the image. I have fetched the data grouped by VesselID, with max function applied on cargodate(Date), as shown in the next image. Now I need balance of ...
0
votes
2answers
69 views

Calculating Margin in CASE Statement and GROUP BY Clause

I think I am running into an error in how I have chosen to aggregate my columns in a group by statement. I am working out Margin % and grouping into months using a case statement like so: SUM(CASE ...
0
votes
1answer
25 views

How can I group two joined tables

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL fk_id_chest 18059 18.56 R12 1 18060 11 R10 2 18061 15 ...
0
votes
1answer
35 views

How can I group (by) two joined tables [closed]

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL 18059 18.56 R12 18060 11 R10 18061 15 R11 col: FK_ID_COL_DMA ...
0
votes
1answer
41 views

Using group by on several tables with problems [closed]

I have a poorly-designed table in an Oracle database like this: sr: id an_rap 21 2015 rap: id cf 28185 123-123 ch: id fk_id_rap FK_ID_SR 12348 28185 21 dma: ch_id ...
0
votes
2answers
56 views

Order By Case String Not Working

I am trying to get my SQL search to work. What I am looking for is a search result that pulls information based on either a company's name or their stock trading ticker. If one would to type in M, I ...
4
votes
1answer
148 views

How to avoid invoking functions twice when using GROUP BY and HAVING?

I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents. The following query works and returns the correct results: ...
0
votes
1answer
38 views

Update rows in a table with an index column starting from 0 with consecutive numbers

I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a ...
0
votes
0answers
27 views

Joining one table with multiple tables for aggregate functions possible?

I am trying to get this seemingly simple looking query to work : SELECT c.id, c.name, count(chc.id) as contacts_count, count(cha.id) as addresses_count FROM (customers c LEFT JOIN ...
1
vote
0answers
46 views

Find the last record of group of overlapped records

I have a little dilemma with filtering out result. The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem: I ...
0
votes
2answers
48 views

sql problem with multiple linked tables

I have an Oracle db and the following tables: raportor: id id_pct_lucru 58382 327342 ses_rap id an status 1 2012 I 21 2013 I 41 2014 D ch_trat id fk_id_raportor ...
-3
votes
1answer
62 views

Finding the rows with the minimum value per group [duplicate]

How do I get the min timestamp value of the same Id number with its corresponding Value using SQL? Would anybody help me with this problem? (Please see attached image below) Sample Table Sample ...
-6
votes
1answer
56 views

Minimum value per group [closed]

How do I get the min timestamp value of the same Id number using SQL? (please see below picture) Sample Table Sample Table of what should be the result
0
votes
0answers
24 views

Query segment of database based on overall range of columns?

Purpose We're running a bunch of re-engagement experiments where we send different emails to our users and test which are most effective. In order to test these different emails we can't just send ...
4
votes
1answer
59 views

Adding a second Group By column slows query down

I have the following query: SELECT 'TEXT', 'TYPE', 'DESTINATION', 'HEADLINE', 'COUNTRY_ID', 'COUNTRY_CODE' UNION ALL SELECT Data_1.TEXT, Data_1.TYPE, Data_2.Destination, Data_2.HEADLINE, Data_3....
1
vote
2answers
173 views

Multiple COUNT fields, GROUP BY

I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries? I'm aware of WITH ...
1
vote
1answer
750 views

2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

Ref : http://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql I got answer (thanks coytech ) but i need one more column in its : as per : id | mid | pid | owgh | nwgh | ...
0
votes
1answer
42 views

Selection of first and last in non grouped item in SQL Server [closed]

Here's my table structure Id ToolName ToolKey ToolStatus ----------------------------------- 1 XXXX 123 Running 2 XXXX 123 Completed 3 yyyy 134 Running ...
0
votes
0answers
54 views

MySQL: How to count non-unique rows in a multiple criteria COUNT query?

I want to calculate, in one query, the percentage of researchers (chercheurs) that have co-signed written publications (between two dates, only for a type of publications and only for researchers ...
2
votes
1answer
72 views

GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions

Tables: content tag_map extrainfo |id|title| |id|tagid| |id|lat|lng| |--|-----| |--|-----| |--|---|---| |1 |Lorem| |1 |8 | |3 |0,4|0,1| |2 |Ipsum| |2 |9 | |...
4
votes
1answer
2k views

Select when group by contains more than one row with the same value?

I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many ...
4
votes
3answers
169 views

Return even empty groups when grouping by date

I have the following query: SELECT DISTINCT COUNT(CD_BarCode) ,CD_Carrier ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date FROM TB_AGIL WHERE ...
2
votes
2answers
40 views

Grouping in long tables

My application is running over Postgresql 9.4 and I found a problem and I don't know how can I fix it. I'm runing VoIp application, and we have a few tables that are too long (>60M of rows) with all ...
1
vote
2answers
88 views

group_concat in separate columns

I have a players table id name -- ---- 1 jack 2 john 3 mike 4 pikachu And I have a matches table where these players can be matched together. id pair_id player_id -- ------- --------- 1 ...
0
votes
1answer
31 views

How can I get a single row on a inner join not to double my sum

I have two tables (DEP_DEZ & CAR_EXP): ID FK_ID_CHESTIONAR TOTAL_E TOTAL_F TOTAL_STOC 261 644 87 152 48 ID MASA_TOTALA FK_ID_CHESTIONAR 405 20 644 406 45 ...
-1
votes
1answer
84 views

Query to group by column and count number of days based on dates

Sample data: id|location|date 1|TUNDUMA |16/8/2015 2|TUNDUMA |16/8/2015 3|TUNDUMA |17/8/2015 4|TUNDUMA |18/8/2015 5|TUNDUMA |19/8/2015 Is it possible to count the number ...
3
votes
2answers
96 views

How does TOPN influence a query with group by?

I have a query that looks like the following: Select t1.field1, t2.field2, sum(t1.field3) From t1 inner join t2 on t1.id = t2.id Group by t1.field1, t2.field2 It's simplified, normally there are a ...
3
votes
3answers
213 views

Finding contiguous ranges in grouped data

I have a table with the following structure: CREATE TABLE `Rings` ( ID_RingType CHAR(2), Number MEDIUMINT UNSIGNED, ID_User INT(11) ); and with data: INSERT INTO `Rings` VALUES ('AA',...
1
vote
1answer
58 views

Sum currencies excluding duplicatied records

I have a CRMOffer table containing customerID and other CRMOffer data. And a Sell table containing customerID and sellValue and sellPK. Sell table has a explicit PK, CRMOffer sadly doesn't have one ...