GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

1
vote
1answer
56 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
1k 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
72 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
45 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 ...
0
votes
1answer
133 views

Using GROUPing or ROLLUP rather than SUMing every column

I am currently looking for a way to achieve what this query is doing without using SUM for every column and, if possible, GROUPing or using ROLLUP or something similar. This query gives me a total ...
1
vote
2answers
55 views

group result of multiple table to get unique result after count operation

survey_categories +---------------+-------------------+ | survey_cat_id | survey_cat_status | +---------------+-------------------+ | 1 | 1 | | 2 | ...
-2
votes
1answer
534 views

Sorting in dd/mm/yyyy format group by date

select CONVERT(nvarchar(15),TransDate,103) as TransDate, isnull(SUM(Convert(int,Amount)),'0') as Amount from HMS_DiagnosisTransactions where TransDate between '05/01/2015' and '07/24/2015' ...
1
vote
2answers
101 views

Get the nth distinct column value ordered by another column

I want to get the row with the nth distinct value in a column, as ordered by another column. In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the ...
3
votes
1answer
268 views

COUNT number of column values with INNER JOIN [closed]

I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle: http://sqlfiddle.com/#!15/6ce465/5 So basically I have two tables, responses and questions. The structure is responses ->...
1
vote
2answers
174 views

Proper usage of Group by in Inner Join

EDIT I updated my question with a SQL Fiddle Sample http://sqlfiddle.com/#!15/8d88b/1 I'm currently making a report from a database records but I don't know how my query should look like, first of ...
1
vote
1answer
57 views

How to group data and write it's group id for each row?

I have sample data: CREATE TABLE #T (Name varchar(5), GroupId int NULL) INSERT INTO #T (Name) VALUES ('A'), ('A'), ('A'), ('B'), ('B'), ('C'), ('D'), ('D') Name GroupId ----- ----------- A ...
2
votes
2answers
210 views

Group and order output into a single row

I want to display each person's contact info and their commissions in a single row as opposed to multiple rows. Commission Table ID COMMISSION DATE ...
0
votes
0answers
29 views

Problems Calculating and Grouping Results Accurately with timediff

Query Function time_to_sec (TIMEDIFF(ohrm_attendance_record.punch_out_user_time,ohrm_attendance_record.punch_in_user_time))/3600 AS time_to_sec_TIMEDIFF_ohrm Results should total to 4.35 Right? but ...
0
votes
1answer
30 views

How do I figure TOTAL DURATION for specific group

I have a table with CARD # and REDEEMED DATE. I am trying to figure out the total duration (in minutes) for each CARD #. I am using Microsoft SQL Server 2014
1
vote
1answer
196 views

Optimizing Queries HAVING COUNT and INSERT…ON DUPLICATE KEY UPDATE

There are two main queries I'm tying to improve, one question is much more conceptual and learning about databases and another is much more straightforward. Both cases deal with the same table It has ...
0
votes
1answer
84 views

How to do a 'SUM' of a COUNT(*) in your select to be grouped by a particular column in SQL SERVER

What I am looking for is to be able to SUM the open_lines and group them by that so instead of 'cntnr_id' having open_lines showing as '1' it should show '2' since it is the same cntnr_id and ...
0
votes
1answer
49 views

Group-by in an Oracle database

on an Oracle database. If I make an: select id,id_operator,denumire,cod_fiscal,loc_den,str from rap where is_punct_lucru=1 and id_operator in ( select id_operator from rap where ...
0
votes
2answers
132 views

MYSQL Select Distinct with SUM

I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this: ---------------------------------------- | ...
1
vote
4answers
235 views

SQL retrieve last elements of table using group by like behaviour

I am trying without success to retrieve the last version of all articles in a table. The table structure: CREATE TABLE `journalarticle` ( `uuid_` varchar(75) DEFAULT NULL, `articleId` varchar(75)...
0
votes
2answers
2k views

Use column alias in GROUP BY

I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly? SELECT KOS_VER_ID AS "...
0
votes
0answers
43 views

Get last updated date and group by in mysql

trans_id | item_id | price | last_updated 01 |001 | 100 | 2015-01-01 02 |002 | 200 | 2015-01-02 03 |001 | 150 | 2015-01-03 03 |003 | 200 | 2015-01-...
1
vote
1answer
87 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
0
votes
1answer
56 views

mysql Quiz leaderboard filter by points, time taken

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken. ...
1
vote
1answer
353 views

mysql: counting number of tickets which are open per day basis

I have a table tabName with the following rows: id(PK) .. Created(datetime) Resolved(datetime) Status(new/resolved/open) 1 2015-05-10 00:00:00 1970-01-01 .. open 2 2015-...
0
votes
1answer
38 views

How to remove some data not needed on new row from mysql query with rollup

Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last ...
3
votes
4answers
408 views

Mysql Order By affecting a Query with column alias, group by, and having

Description: I have a query with column alias, group by and having. It's getting altered when I add an order by. This strange behavior is only happening in 5.6.16 and 5.6.21 (which I was using in ...
1
vote
1answer
225 views

Group by maximum consecutive row

I have data like the following: id | src_ip | dst_ip | port | ----|----------------|--------------|------| 256 | 192.168.61.200 | 10.75.64.222 | 80 | 257 | 192.168.61.200 | 10.75.64....
1
vote
1answer
1k views

Query with group by having count 0

I am working on application for managing football league as my homework. I want filter list of players by number of matches they have played in league. For example user can enter count > 0 and count &...
-1
votes
1answer
917 views

Group by function not working correctly in SQL server CTE [closed]

This is SQL Server CTE, ;with grp (Sdate,TransactionType,tot) as (select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type], (count ([Transaction_type])) as [Total Count] ...
1
vote
1answer
64 views

select group by with multiple AND conditions on the same columns

I've an sql table like this: SID|SERVICE_SID|ATTRIBUTE_SID|VALUE 1 2 5 "VAL1" 2 2 4 "VAL8" 3 2 3 "VAL4" 4 11 5 ...
0
votes
1answer
256 views

Optimization for aggregation and grouping with MySQL

I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc.. I'm having some issues avoiding ...
0
votes
1answer
48 views

Restricting multiple records to one by preference?

Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables. SELECT products.UPC, T2.Price1, vProducts_PriceQty_Union.Price2, ...
0
votes
0answers
142 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
0
votes
1answer
26 views

Group table records by Item than Year

I have a SQL table that is layed out as follows: {Itemcode} {FiscalYear} {FiscalMonth} {StartingQty} {PeriodChangeQty} Ball 2013 01 15000 -7500 Ball 2013 02 0 1000 Ball ...
5
votes
1answer
168 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, non-...
0
votes
1answer
18 views

GROUP BY parentId and MAX childId

My tables are below: crm_conversions crm_messages_of_conversions crm_receivers_of_messages I want to get all of conversions of an user with only last message. I'm trying to get with the query: ...
0
votes
2answers
237 views

Optimizing an AVG / GROUP BY query

Here is the query: SELECT p.domain_id AS comp, AVG(IFNULL(p.position, 50)) as AVG, p.date AS period FROM userdomain ud LEFT JOIN userdomainexpression ude ON ude.userdomain_id=ud.id ...
1
vote
1answer
107 views

MySQL Update a table based on the last data from another table while grouping with conditions

I need a query to UPDATE a series of fields based on another table Table lp_plates_backup | uid | brand | model | | 1 | | | | a | Old | Error | |...
0
votes
1answer
902 views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
0
votes
0answers
133 views

MySQL - Group By - How does it determine which row to return from joined table

I am getting desired, but inexplicable behavior on a particular query, and wanted to get more information before pushing up. I have 3 tables: foo bar bar_image And paring back all distractors, ...
0
votes
1answer
615 views

Group by without temporary table and filesort

My aim is to make the following query faster. SELECT SQL_NO_CACHE Links.id,Links.link,sum(ID_Links_Words.count) AS wordCount FROM Links LEFT JOIN ID_Links_Words ON Links.id = ID_Links_Words.link ...
0
votes
2answers
60 views

Grouping rows by looking at two columns without considering the order and summing each separately

I have a table looks like this one below: +---------+---------+-------+ | SOURCE | DEST | VALUE | +---------+---------+-------+ | A | B | 1 | | B | A | 2 | | A ...
0
votes
3answers
218 views

Group by transformed column

I have a database with creations and orders, and a bunch of tables linking them. I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a ...
1
vote
1answer
215 views

Filter mysql group by negative having contains

How can I get all of the values from 1 column, which do not (or do) have a specific value in one of their rows? For instance, consider example table: | ColA | ColB | --------------- | aaa1 | zzz1 | |...
0
votes
1answer
153 views

GROUP BY on two large joined tables

I have two tables: Person ------ PersonID (PK) GroupID DeletedDate PersonData ---------- PersonID (PK) FieldID (PK) Value The fields are custom-created; hence the schema. I am trying to run a ...
0
votes
1answer
95 views

Multiple records and determine if a combination if values exist and write this to a new column

I'm struggling with a problem. An order can have multiple lines and accordingly a type (F=Footwear/N=Non-FTW,Mixed=Both) I need to determine if the order consists of which type and create a new ...
1
vote
0answers
269 views

Simple avg query on large table much slower in PostgreSQL than SQL Server

I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012). ...
0
votes
1answer
279 views

Sum of a column that is already a sum

This is my query: select d.department_id, d.department_name, sum(e.salary) AS total from department d inner join employee e on d.department_id=e.department_id group by d.department_id,d....
3
votes
1answer
2k views

How to use a group by in inner join

I have Two tables: Table 1 ID totalamount --- ----------- 100 1000 101 500 Table 2 ID Individualamount --- ---------------- 100 500 100 300 100 100 101 200 101 300 I Need a result ...
0
votes
1answer
96 views

Getting sum result for last year divided quarterly

I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks. Also, if it is possible, I want to have the type as a separate column with the type as ...