Menu
I have two tables; let's call them TableA and TableB. Each element of TableB is associated with an element of TableA in a many-to-one relationship. Some elements of TableA may have no corresponding element in TableB.
I need to select every element in TableA, along with a column that will, for each row in the result set, contain the number of elements in TableB that are associated with that row in TableA, and that have a certain property.
Using MS SQL Server 2008 (Needs to also work in SQL Server 2005).
645
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
As you've already learned, COUNT(*) will count all the rows in the table, including NULL values. On the other hand, COUNT(column name) will count all the rows in the specified column while excluding NULL values. FROM orders; Do we get the same result?
Four types of joins: left, right, inner, and outer.
Different Types of SQL JOINs (INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
Updated
SELECT A.elementid, A.column1, A.column2, A.column3,
COUNT(CASE WHEN B.someColumn > 0 THEN B.elementid ELSE NULL END) Q
FROM TableA A
LEFT JOIN TableB B
ON A.elementid = B.elementid
GROUP BY A.elementid, A.column1, A.column2, A.column3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
