GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
0answers
19 views

Query to Group By for a Specific Substring returning Only One Result [on hold]

I have the following query: Select count(*) as countForType, SUM(typeTable.amount) as totalForType, case when ( charindex('[', typeTable.source) > 0 and ...
1
vote
2answers
26 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
18 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
50 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
56 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
29 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
66 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 ...
-4
votes
0answers
75 views

how to get one of the values in group by?

For the following tables stock_moves product_id purchase_line_id state product_uom_qty ----------------------------------------------------------- 4018 1230 assigned ...
2
votes
1answer
56 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
110 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
67 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
44 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 ...
1
vote
0answers
26 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
41 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, ...
0
votes
1answer
27 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
31 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. ...
0
votes
1answer
71 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
21 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
57 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
23 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
30 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
39 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
37 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
66 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
32 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
20 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
34 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
61 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
48 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
17 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
54 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, ...
1
vote
2answers
76 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
142 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
41 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
48 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
68 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
498 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
118 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
36 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
63 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
25 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
61 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
90 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
180 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 ...
1
vote
1answer
42 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 ...
1
vote
1answer
46 views

Optimize join - group by Oracle SQL filter

I have a few tables: CHEST: id fk_id_rap fk_id_ses_rap 1 1 1 2 2 1 3 3 1 SES_RAP: id an 1 2015 2 2016 RAP: id name 1 rap1 2 rap2 3 ...
4
votes
3answers
536 views

group_concat and group by together

I am trying to write a query using group_concat, concat and other functions, but I am getting stuck not able to join and group the data to represent them in 1 cell. Here's how my data is... table1 ...
1
vote
2answers
49 views

Group by month in result columns?

I'm using this DBMS: Caché I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need. Given the following sample data: ID Name Result Date ...
1
vote
2answers
40 views

Apply limit and conditions per grouped data

I am building an api endpoint to send statistics data that then is going to be plotted on a graph. The hardest part is being the query I need to execute. To sum up I want the first n most sold ...