Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
SELECT col1, col2 FROM schema.table WHERE col3 > 10 AND col4 IS NOT NULL ORDER BY col1 DESC;
SQL Server: SELECT TOP 10 .... MariaDB: ... LIMIT 10.
WHERE x = 5 WHERE x <> 5 WHERE x BETWEEN 1 AND 9 WHERE x IN (1, 2, 3) WHERE name LIKE 'Ab%' WHERE x IS NULL
Never = NULL. Combine with AND, OR, NOT (use parentheses).
FROM orders o INNER JOIN customers c ON c.id = o.customer_id LEFT JOIN payments p ON p.order_id = o.id
INNER keeps matches only. LEFT keeps every row on the left. Always give the ON condition.
SELECT category, COUNT(*) AS n, SUM(amount) AS total FROM sales GROUP BY category HAVING SUM(amount) > 1000 ORDER BY total DESC;
WHERE filters rows before grouping; HAVING filters groups after.
WITH big AS ( SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id) SELECT * FROM big WHERE total > 5000; SELECT a FROM t1 UNION SELECT a FROM t2;
UNION removes duplicates; UNION ALL keeps them.
SELECT order_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn, SUM(amount) OVER (ORDER BY order_date) AS running, AVG(amount) OVER (PARTITION BY customer_id) AS cust_avg FROM sales;
They calculate across related rows without collapsing them.
UPPER(x) LOWER(x) TRIM(x) LEN(x) -- LENGTH() in MariaDB CONCAT(a, ' ', b) SUBSTRING(x, 1, 3) ROUND(x, 2) COALESCE(x, 0) CAST(x AS int) YEAR(d) MONTH(d) DATEADD(day, 7, d) -- DATE_ADD() in MariaDB
CASE WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C' END AS grade
| SQL Server | MariaDB | |
|---|---|---|
| Row limit | TOP 10 | LIMIT 10 |
| Quote a name | [Order Details] | `Order Details` |
| Other database | db.schema.table | db.table |
| Today | GETDATE() | NOW() |
| Text join | + or CONCAT | CONCAT |
Total Sales = SUM(Sales[SalesAmount]) Margin % = DIVIDE([Gross Margin], [Total Sales]) Orders = DISTINCTCOUNT(Sales[OrderID]) Revenue = SUMX(Sales, Sales[Qty] * Sales[Price])
Columns are Table[Column], measures are [Measure]. Use DIVIDE, not /.
Margin % = VAR S = [Total Sales] VAR C = [Total Cost] RETURN DIVIDE(S - C, S)
Evaluated once, readable, and fast.
Online Sales = CALCULATE([Total Sales], Channel[Channel] = "Online") All Channels = CALCULATE([Total Sales], REMOVEFILTERS(Channel))
Boolean filters replace the column's filter. KEEPFILTERS intersects instead.
% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS())) % of Selected = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Category)))
ALL ignores the slicer; ALLSELECTED respects it.
Sales YTD = TOTALYTD([Total Sales], 'Calendar'[Date])
Sales PY = CALCULATE([Total Sales],
SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])
Rolling 12M = CALCULATE([Total Sales],
DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))
Needs a Calendar table marked as the date table.
Rank = RANKX(ALLSELECTED(Product[Product]), [Total Sales], , DESC, Dense) Top 10 = CALCULATE([Total Sales], TOPN(10, ALLSELECTED(Product[Product]), [Total Sales]))
Brand = RELATED(Product[Brand]) -- calculated column Ship Date Sales = CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Calendar'[Date]))
SELECTEDVALUE(Col, "All") HASONEVALUE(Col) ISFILTERED(Col) ISINSCOPE(Col) VALUES(Col) COUNTROWS(table)
SUMX, not SUM(a*b).FILTER.IFERROR hides real errors; test with DIVIDE or ISBLANK.FORMAT in a measure turns numbers into text.More depth in the Analyze guides, the function reference and the glossary.