Hot answers tagged group-by
20
GROUP BY A.* is not allowed in SQL.
You can bypass this by using a subquery where you group by, and then join:
SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
LEFT JOIN
( SELECT FKey, COUNT(foo) AS cnt
FROM TABLE2
GROUP BY FKey
) AS B
ON A.PKey = B.FKey ;
There is a feature in SQL-2003 standard to ...
17
In addition to @ypercube's workaround, "typing" is never an excuse for using SELECT *. I've written about this here, and even with the workaround I think your SELECT list should still include the column names - even if there are a massive number like 40.
Long story short, you can avoid typing these big lists by clicking and dragging the Columns node for the ...
8
You say: "My best educated guess is that somehow max is being used to avoid multiple grouping columns"
That is correct.
and then: "... but how can this return the correct results?"
It returns correct results because the Symbol is the primary key in both the Investments and the Price tables. Therefore, any aggregate function over a P.column or an I.column ...
7
This is a fairly generic way to do this.
Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:
SELECT
number
FROM
mytable m
CROSS JOIN
(SELECT 3 AS consec) x
WHERE
EXISTS
(SELECT 1
FROM mytable
WHERE number = m.number - ...
6
You are not showing the query you are using to obtain the results without diff. I'm assuming it is something like this:
SELECT
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value), -- or, if Value is an int, like this, perhaps:
-- AVG(CAST(Value AS decimal(10,2))
Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
...
6
First things first, I notice that your 'what I do now' query:
SELECT TOP (1)
ca.SensorValue,
ca.Date
FROM sys.partitions AS p
CROSS APPLY
(
SELECT TOP (1)
v.Date,
v.SensorValue
FROM SensorValues AS v
WHERE
$PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
AND v.DeviceId = @fDeviceId
...
6
You probably want something like this:
SELECT h_v_charges.*,
last_v.last_version
FROM hist_versions_charges h_v_charges
JOIN (select proj_charge_id,
max(version) as last_version
from hist_versions_charges
where proj_sous_projet_id = 2
group by proj_charge_id
) last_v
ON h_v_charges.version = ...
5
I infer that your data looks like this:
Person Table
╔══════════╦═══════╦════════╗
║ PersonID ║ Name ║ Gender ║
╠══════════╬═══════╬════════╣
║ 1 ║ John ║ M ║
║ 2 ║ Vicky ║ F ║
║ 3 ║ Bob ║ M ║
╚══════════╩═══════╩════════╝
Job Table
╔══════════╦═════════════╦════════════╗
║ PersonID ║ JobName ║ HireDate ║
...
5
The LEFT JOIN in @dezso's answer should be good. An index, however, will hardly be useful (per se), because the query has to read the whole table anyway - the exception being index-only scans under PostgreSQL 9.2 and favorable conditions, see below.
SELECT m.hash, m.string, count(m.method) AS method_ct
FROM methods m
LEFT JOIN nostring n USING (hash)
...
5
Unless I am missing something, your query would be something like this:
select created, count(*) CreatedCount
from yourtable
group by created
order by created;
See SQL Fiddle with Demo
Or if you have a time associated with the date, you can use TRUNC:
select trunc(created), count(*) CreatedCount
from yourtable
group by trunc(created)
order by ...
5
The answer can be platform-specific. Although the results are the same, your performance might be very different.
I would suggest another approach, because it seems to clearly document the following intent: calculate some aggregates, then decorate them with another field(s). Here is an example:
WITH CustomerTotals AS(
SELECT CustomerID,
SUM(Amount) AS ...
5
Use correct ANSI group by (not the MySQL abomination extension) and see what happens
select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by name,gender,dob,country
having sum(score)>=1000
order by sum(score) desc limit 50
Why?
GROUP BY in ...
4
Another way to do this if the columns are known is using an aggregate and a CASE statement:
SELECT d,
sum(case when loc = 'Baltimore' then v else 0 end) as Baltimore,
sum(case when loc = 'Houston' then v else 0 end) as Houston,
sum(case when loc = 'Chicago' then v else 0 end) as Chicago
FROM test
group by d
order by d;
See SQL Fiddle with Demo
If ...
4
It could be that the aggregations fit better in a subquery, and make more sense when it's that subquery that's later joined to the other table INVESTMENTS and PRICE-see below.
In that case you could say that the "reason" for it could be:
developer inexperience w/ SQL
developer experience gained on SQL platform that doesn't support table subqueries
...
4
SELECT type,
GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END
ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END
ORDER BY id ASC SEPARATOR ' ') AS list_without_info
FROM table1
GROUP BY type ;
Tested at SQL-Fiddle: test-1
...
4
This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set:
WITH partitioned AS (
SELECT
*,
number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
FROM atable
WHERE status = 'FREE'
),
counted AS (
SELECT
*,
COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
FROM partitioned
)
SELECT
id_set,
...
4
A simple and fast variant:
SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
FROM tbl
WHERE status = 'FREE'
) x
GROUP BY grp
HAVING count(*) >= 3 -- minimum length of sequence only goes here
ORDER BY grp
LIMIT 1;
Requires a gapless ...
3
As @Back in a Flash has suggested, an index on created_at in user_articles will probably help if the optimizer chooses to use it... but the optimizer might not catch on to the viability of that index, because you're doing something else that needs an explanation.
You're using GROUP BY but you're not aggregating anything that I can see. If you're not ...
3
To use a normal PIVOT operation (in Oracle 11g R2), you need to know the locations:
with foo as
(
select d,loc,v
from test
)
select *
from foo
pivot (
sum(v)
for loc in ('Baltimore','Houston','Chicago')
)
order by 1;
If you don't know all locations there are hacky ways of doing it, but they're not pretty. This Oracle forum thread has (hacky) ...
3
Looks like you want something like:
SELECT country
, sum(case when type = 'first' then 1 else 0 end) as type_first
, sum(case when type = 'second' then 1 else 0 end) as type_second
, sum(case when type = 'third' then 1 else 0 end) as type_third
FROM table1
GROUP BY country
3
This will return only the first of the 3 numbers. It does not require that the values of number are consecutive. Tested at SQL-Fiddle:
WITH cte3 AS
( SELECT
*,
COUNT(CASE WHEN status = 'FREE' THEN 1 END)
OVER (PARTITION BY id_set ORDER BY number
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
AS cnt
FROM atable
)
SELECT
...
3
Things to try:
Adding an index on (user_id, date, score)
Group by only on scores table and then join to users:
SELECT s.total, u.name, u.gender, u.dob, u.country
FROM users AS u
JOIN
( SELECT user_id, SUM(score) AS total
FROM scores
WHERE date >= '2012-01-01' AND date < '2012-02-01'
GROUP BY user_id
HAVING SUM(score) >= 1000
...
2
Your query can return the expected results by adding the reverse condition:
SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM persons A
LEFT JOIN persons B
ON a.ID <> b.ID
AND (a.Name LIKE CONCAT ("%", b.Name, "%") OR b.Name LIKE CONCAT ("%", a.Name, "%"))
ORDER BY ID;
I don't know if it will be faster, but another way to do it would be to use ...
2
SELECT
country,
SUM(IF(A.type='first' ,country_type_count,0)) type_first,
SUM(IF(A.type='second',country_type_count,0)) type_second,
SUM(IF(A.type='third' ,country_type_count,0)) type_third
FROM
(
SELECT country, type, COUNT(*) country_type_count
FROM table1 GROUP BY country, type
) A
GROUP BY country;
2
Welcome to DBA.SE!
You can try to rephrase your query like this:
SELECT m.hash, string, count(method)
FROM
methods m
LEFT JOIN nostring n ON m.hash = n.hash
WHERE n.hash IS NULL
GROUP BY hash, string
ORDER BY count(method) DESC;
or another possibility:
SELECT m.hash, string, count(method)
FROM
methods m
WHERE NOT EXISTS (SELECT hash ...
2
I guess what you want is this, because it would make sense:
SELECT DISTINCT ON (c.name)
c.name, min(s.synonym) AS min_synonym, s.cid
,ts_rank(s.tsv_syns,c.lexemes,16) AS rnk
,count(*) AS ct
FROM synonyms_all_gin_tsvcolumn s
JOIN cmap5 c ON c.lexemes @@ s.tsv_syns
GROUP BY c.name, rnk, s.cid
ORDER BY c.name, rnk ...
2
So why does Postgres 9.2 still show a sequential scan? I quote the Postgres Wiki:
Is "count(*)" much faster now?
A traditional complaint made of PostgreSQL, generally when comparing
it unfavourably with MySQL (at least when using the MyIsam storage
engine, which doesn't use MVCC) has been "count(*) is slow".
Index-only scans can be used to ...
2
PostgreSQL doesn't directly support PIVOT, which is the keyword usually used on other platforms for something like this. It does have some crosstab functions in the tablefunc module.
Using the crosstab() function, (an excellent StackOverflow answer)
2
If you have a limited number of values that you want to convert into columns, then this can easily be implemented using an aggregate function with a CASE expression:
select user_id,
sum(case when message_type = 'private' then 1 else 0 end) private,
sum(case when message_type = 'public' then 1 else 0 end) public
from yourtable
group by user_id
See SQL ...
2
With the comment from FrustratedWithFormsDesigner, I came to the following solution:
SELECT subq2.*, sum(new_group) OVER (ORDER BY t ASC) AS group_id
FROM (
SELECT subq.*, CASE WHEN delta > 1500 THEN 1 ELSE 0 END AS new_group
FROM (
SELECT t, lag(t) over (ORDER BY t ASC),
t - lag(t) over (ORDER BY t ASC) AS delta
FROM time_points
) AS ...
Only top voted, non community-wiki answers of a minimum length are eligible
