New answers tagged count
0
A query of this basic form only needs a single sequential scan and should be as efficient as it gets for the general case:
SELECT x.*
FROM (
SELECT count(*) AS ct
, count(ticker) AS ticker
, count(profits_change) AS profits_change
, count(industry) AS industry
-- , more?
FROM fortune500
) t
CROSS JOIN LATERAL (
...
1
You can SELECT the DISTINCT member ids before joining
CREATE TABLE members (
`id` INTEGER,
`full_name` VARCHAR(15),
`sex` INTEGER
);
INSERT INTO members
(`id`, `full_name`, `sex`)
VALUES
('1', 'John Smith', '1'),
('2', 'Carol Danvers', '2'),
('3', 'Clark Kent', '1'),
('4', 'Lois Lane', '2'),
('5', 'Diana Prince', '2');
CREATE TABLE ...
1
You'll want to use a window function such as ROW_NUMBER() for this one like so:
SELECT CUSTOMER_ID, TRANSACTION_ID, ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY TRANSACTION_ID) AS [Count] -- Running incremental count of rows / TRANSACTION_ID per CUSTOMER_ID
FROM TRANSACTIONS
GROUP BY CUSTOMER_ID, TRANSACTION_ID
ORDER BY CUSTOMER_ID, TRANSACTION_ID
0
Check this:
SELECT mt.id main_id, mt.name main_name, st.id sub_id, st.name sub_name
FROM main_table AS mt
LEFT JOIN sub_table st ON mt.id = st.main_table_id
WHERE st.middle_table_id IS NULL
-- AND st.main_table_id IS NOT NULL
UNION ALL
SELECT mt.id, mt.name, st.id, st.name
FROM main_table AS mt
LEFT JOIN middle_table ON mt.id = middle_table.main_table_id
...
0
This should accomplish what you're looking for but this assumes all of your eCodes exist at least for one record in your Test table. Otherwise you can replace the Test table instance on the right side of the CROSS JOIN in the CTE below with your source table of eCodes:
WITH CTE_EmployeeECodes AS -- Gets a distinct list of all Employee and eCode combinations
(...
1
You can use WINDOW functions they exist since 10.2
CREATE TABLE student_points
(`student_no` int, `level_id` int, `points` int)
;
INSERT INTO student_points
(`student_no`, `level_id`, `points`)
VALUES
(12345678, 1, 70),
(10983023, 2, 90),
(24829039, 1, 20),
(12345678, 1, 70),
(10983023, 2, 20),
(24829039, 1, 20),
...
Top 50 recent answers are included
