DECLARE @orders TABLE (
category VARCHAR(5),
express_delivered INT
);
INSERT INTO @orders (category, express_delivered) VALUES ( 'food', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'food', 0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'shoes',0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'shoes',0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'auto', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'auto', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'book', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'book', 0);
SELECT
category,
CASE WHEN SUM(express_delivered) = 0 THEN 0 ELSE 1 END AS ever_been_express_delivered
FROM @orders
GROUP BY categorySo:
CASE WHEN SUM(express_delivered) = 0 THEN 0 ELSE 1 END AS ever_been_express_delivered
becomes:
IIF(SUM(express_delivered) = 0, 0, 1) AS ever_been_express_delivered
select category case when sum(express_delivered) = count(express_delivered) then 1 else 0 end as always_been_express_delivered from etc.
SELECT
category,
ISNULL(NonExpress.NonExpress, 0) AS ever_been_non_express_delivered
FROM @orders O
OUTER APPLY (
SELECT TOP 1 1 as [NonExpress]
FROM @Orders O2
WHERE O2.Category = O.Category
AND express_delivered = 0
) NonExpress
GROUP BY category
CASE WHEN MIN(express_delivered) = 0 THEN 0 ELSE 1 END AS always_been_express_deliveredWith `json_build_object()`, you can select fields as a JSON object and not have to do that JSON building in your app.
Here's a snippet demonstrating building, and then querying a JSON object:
with location as (select json_build_object(
'street1', street1,
'street2', street2,
'city', city,
'state', state,
'zip', zip
) as loc from my_table)
select
loc ->> 'street1' as street1,
loc ->> 'street2' as street2,
loc ->> 'city' as city,
loc ->> 'state' as state,
loc ->> 'zip' as zip
from location;I guess my question is, to you, what's the main benefit of building JSON in the inthe DB instead of your app?
There is an obvious trade off of flexibility, but this technique is beneficial in that it allows rich object graphs through the ORM where required, while performing well for simple operations.
SELECT cateogry FROM orders WHERE express_delivery=TRUE GROUP BY category
Also are these aggregate functions as efficient?
This is where a more effective question would be more useful.
A question like 'in the last quarter, how many sales did we have for each category and shipping type?' You can then take the results and calculate more useful values like the percentage of express shipments, etc.
But they seem quite handy for searching for bad data in the database, rather than analyzing customer behavior.
I don't believe GROUP BY n1,n2.. should be allowed, because it inverts the flow of control of the query. If you think about it, you're telling SQL to group by a column you haven't specified yet, using the order it appears in the SELECT clause. Someone could edit the query and add a new column at the front of the select list and completely change the meaning of the query. I think the ORDER BY clause has similar issues, but at least that occurs after the SELECT happens.
tl;dr I shouldn't have to look at the ordering of the select to understand what has been grouped by.
Order of SQL operations for the uninitiated, this explains why you can't, for instance, reference a column alias defined in a SELECT clause in the WHERE clause.
6 SELECT
1 FROM
2 JOIN
3 WHERE
4 GROUP BY
5 HAVING
7 ORDER BY