GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

4
votes
3answers
66 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
31 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
42 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
15 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
25 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
70 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
142 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
30 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
37 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
113 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
35 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
38 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
29 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
42 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
71 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
63 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
46 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
37 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
34 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
179 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 ...
1
vote
0answers
24 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
14 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
93 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
66 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
37 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
0answers
16 views

Computing View Table based on rows of other tables [duplicate]

I want to compute a specific view, but I think it will be more clear if I show an example: Given this one view: | Group | User | Type | Quantity | |-------|------|------|----------| | 1 | 13 | ...
0
votes
2answers
72 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
89 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` ...
0
votes
2answers
100 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
34 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 | ...
0
votes
1answer
55 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 ...
0
votes
1answer
37 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
98 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 ...
0
votes
1answer
21 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 ...
2
votes
4answers
164 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
112 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 | ...
1
vote
1answer
341 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
136 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
30 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
71 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
40 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
91 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
0answers
166 views

I want to rewrite my MySQL query so that FIND_IN_SET doesn't do a full table scan unless necessary

I want to rewrite my MySQL query so that the last table join doesn't do a full table scan unless necessary. Here's what I mean. Under certain conditions I don't need to select any rows in that join ...
0
votes
1answer
22 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 ...
3
votes
0answers
65 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, ...
0
votes
1answer
16 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
82 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
66 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
130 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
61 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, ...