Menu
I have two tables: Toys and Games.
+--------------------+------------------+ | Field | Type | +--------------------+------------------+ | toy_id | int(10) unsigned | | little_kid_id | int(10) unsigned | +--------------------+------------------+ +--------------------+------------------+ | Field | Type | +--------------------+------------------+ | game_id | int(10) unsigned | | little_kid1 | int(10) unsigned | | little_kid2 | int(10) unsigned | | little_kid3 | int(10) unsigned | +--------------------+------------------+ A little kid can have multiple toys. A little kid can be participating in multiple games at once.
I want a query that will give me the total number of toys + games that a little_kid is involved with.
Basically, I want the sum of these two queries:
SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900; SELECT COUNT(*) from Games WHERE little_kid1 = 900 OR little_kid2 = 900 OR little_kid3 = 900;
Is it possible to get this in a single SQL query? Obviously, I can sum them programmatically, but that's less desirable.
(I realize that the contrived example makes the schema look ineffecient. Let's assume that we can't change the schema.)
905
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
Syntax to combine tables. The simplest way to combine two tables together is using the keywords UNION or UNION ALL. These two methods pile one lot of selected data on top of the other. The difference between the two keywords is that UNION only takes distinct values, but UNION ALL keeps all of the values selected.
Merging tables by columns. Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other).
Compare two tables by using joins. To compare two tables by using joins, you create a select query that includes both tables. If there is not already an existing relationship between the tables on the fields that contain the corresponding data, you create a join on the fields that you want to examine for matches.
Wrap them up and use subqueries:
SELECT (SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+ (SELECT COUNT(*) from Games WHERE little_kid1 = 900 OR little_kid2 = 900 OR little_kid3 = 900) AS SumCount Voila!
186
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
