2
SQL Troubleshooting with Multiple Number in Query
source link: https://www.codesd.com/item/sql-troubleshooting-with-multiple-number-in-query.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL Troubleshooting with Multiple Number in Query
advertisements
I already provide sql fiddle with schema and sample data.
http://sqlfiddle.com/#!2/e9d22/7/0
If I would like to know how many province and how many cities in Thailand.
Country Name | No. Provinces | No. Cities
Thailand | 77 | 1234
I guess that it need to use multiple COUNT(*) but I dont know how to use it.
Anybody know please suggest solution?
You need to use GROUP BY and COUNT:
SELECT c.name, count(distinct p.id) provincecoutn, count(distinct city.id) citycount
FROM country c
LEFT JOIN province p on c.id = p.country_id
LEFT JOIN City on p.id = city.province_id
GROUP BY c.name
Good luck.
Related Articles
UPDATE SQL database with multiple SET?
I'm trying to update my SQL database with multiple SETs. What I'm trying to achieve is to update a customer record in a table. For example, the customer might change their address. Here is my code: <?php $con = mysql_connect("localhost","
Clause 'In' in SQL server with multiple columns
I have a component that retrieves data from database based on the keys provided. However I want my java application to get all the data for all keys in a single database hit to fasten up things. I can use 'in' clause when I have only one key. While w
How to select a single row from a sql table with multiple rows having the same value in a column
I have an sql table which current looks like this: id | Name | Age| City ______________________________ 1 | Mike | 26 | New York 2 | Nick | 32 | London 3 | Anne | 22 | New York 4 | Andy | 24 | Lagos 5 | Nickie | 27 | Paris 6 | Sam | 26 | London I nee
create a SQL table with multiple columns automatically
I must create an SQL table with 90+ fields, the majority of them are bit fields like N01, N02, N03 ... N89, N90 is there a fast way of creating multiple fileds or is it possible to have one single field to contain an array of values true/false? I nee
Can not read Excel file from SQL Server with multiple versions of SQL Server
EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO USE [master] GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_M
SQL removal with multiple joins
I have some what of a challenging delete statement that I need help with. I'm trying to delete multiple duplicate records with multiple joins on the same table. Here is an example of my data versionid(PK) FileID(FK) Version DeleteDate DeleteIndicator
C # ADO.NET Loading SQL Script with Multiple Declarations and Comments
I am building an application in which I will producing some reports based off the results of some SQL queries executed against a number of different databases and servers. Since I am unable to create stored procedures on each server, I have my SQL sc
SQL records with multiple values for a field
What is the best way to design your table schemas for records that may have multiple values for a certain field? For example, a document can have multiple concepts associated with it. Each document can have different numbers of concepts as well. I'm
SQL SP with Multiple Values for an Input
I've inherited a db etc from another developer and need some help. I have the following stored procedure: CREATE PROCEDURE [dbo].[TESTgetSearchResults] ( @ids varchar(100), @Date DateTime = Null, @Date2 DATETIME = Null, @Sort VARCHAR(5), /* ASC or DE
Left SQL joins with multiple lines in a single line
Basically, I have two tables, Table A contains the actual items that I care to get out, and Table B is used for language translations. So, for example, Table A contains the actual content. Anytime text is used within the table, instead of storing act
SQL Pivot with multiple columns
Need help with the pivot clause in sql server 2008. I have a table with this info: Weekno DayOfWeek FromTime ToTime 1 2 10:00 14:00 1 3 10:00 14:00 2 3 08:00 13:00 2 4 09:00 13:00 2 5 14:00 22:00 3 1 06:00 13:00 3 4 06:00 13:00 3 5 14:00 22:00 I want
Express + Node Route with multiple parameters in Query String
I am building an API in Node and am struggling to figure something out. Namely, I know how to build routes of the type /api/:paramA/:paramB. In this case, there are two parameters. The code would be something like this: router.get('/test/:paramA/:par
Select a single ID in the SQL statement with multiple criteria
This question already has an answer here: SQL: how to select a single id ("row") that meets multiple criteria from a single column 7 answers I want to get the post id's wich contain %planet% and %world. With this statement I would get a single 9
Impala - Getting an error with Multiple number of distinct values
I am using CDH-5.4.4 Cloudera Edition, I have a CSV file in HDFS location, My requirement is to perform Real time SQL queries on Hadoop Environement (OLTP). So I decided to go with Impala, I have created MetaStore table to a CSV file, then execuing q
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK