question 5 easy Understanding Query select WeatherConditions , count(WeatherConditions) as TotalAccident from accident where Severity = 'Fatal' group by WeatherConditions order by TotalAccident desc
hi ; in question 4 ; can we use this query SELECT AgeGroup, COUNT([AccidentIndex]) AS 'Total Accident', AVG([AgeVehicle]) AS 'Average Year', CASE WHEN [AgeVehicle] BETWEEN 0 AND 5 THEN 'New' WHEN [AgeVehicle] BETWEEN 6 AND 10 THEN 'Regular' ELSE 'Old' END AS AgeGroup GROUP BY AgeGroup; thanks
First of all, thank you for the video. I am new at this and was searching for a project to do to learn more and start building a resume. But I have encountered a problem while importing the flat file 'vehicle'. It says - The given value 'No' of type String from the data source cannot be converted to type bit for Column 4 [LeftHand]. (Microsoft.Data.SqlClient) Failed to convert parameter value from a String to a Boolean. (Microsoft.Data.SqlClient) Edit- I got it
Thank you for doing this analysis, Also can you please let me know how can i download this data, i tried to download github links provided above but i don't have find the data sets
Hi. In this link there are two CSV files. you can download them and import in your SQL management application (MySQL or MS SQL). github.com/M3hrdad-Dehghan/SQL/tree/main/Exploratory_Data_Analysis_of_Vehicle_Accident_by_SQL
Accident Index is indeed a label in terms of data type. However, it is important to note that the values displayed in a column may not always correspond to the data type. For example, even though Accident Index is shown as a number, it is actually a label that is a combination of letters and numbers. Therefore, it is highly recommended to change the data type accordingly. It is worth mentioning that in the video, the accident index appears to be a scientific number, but it is actually a label.☺
@@Mer_Degan in my case about 110.00 rows of AccidentIndex data converted to scidentific value in the procces of importing data to sql server database. which makes almost impossibble to analyse the data because we can't relate vehicleID's and AccidentIndex. I trided to change data types but doesn't work. couldn't figure out a solution.
Bruh there are null values in column Age Vehicle of vehicle table its not even getting imported in my mysql workbench csv is a headache fr !! Help me out someone
The left-hand column in the vehicle table cannot be imported with the bit or tinyint datatype. Please note that I am using SQL Server Management Studio (SSMS) for my database
Hey i have a small doubt the excel sheet contains dates in different formats and i am not sure how to change them into single format can you help me thanks in advance.
Excel is an application that is used for storing structured data. However, there are other tools available for storing structured data, such as databases, Access, and others. Apart from structured data, there are semi-structured data types, such as websites, and non-structured data types, such as voice, text, and video, that have their own storage systems. The most important step in data management is to understand the data itself, while tools and techniques are secondary.
In SQL, a subquery is a query within another query. It's like a small query nested inside a larger on. This part selects "AccidentIndex" and "AgeVehicle" from the vehicle table. It also creates a new column called "AgeGroup" which categorizes vehicles into 'New', 'Regular', or 'Old' based on their age.In summary, the subquery first categorizes vehicles by age, and then the main query calculates statistics based on these categories.
in question 7 i used this query : WITH AccidentCounts AS ( SELECT V.[JourneyPurpose], COUNT(A.[Severity]) AS 'Total Accident' FROM [dbo].[accident] A JOIN [dbo].[vehicle] V ON A.[AccidentIndex] = V.[AccidentIndex] GROUP BY V.[JourneyPurpose] ) SELECT [JourneyPurpose], [Total Accident], CASE WHEN [Total Accident] BETWEEN 0 AND 1000 THEN 'Low' WHEN [Total Accident] BETWEEN 1001 AND 3000 THEN 'Moderate' ELSE 'High' END AS 'Level' FROM AccidentCounts ORDER BY [Total Accident] DESC;
Hi Simon. It's just about the EDA project using T-SQL. You can find it a useful project for dealing with data to explore more about it. After running EDA on data, you are ready to use one of the BI tools to visualize data to gain more insights. By the way, I planned to run the E-2-E project, but I was tasked with a huge workload. So, I try to publish more videos on different topics.
I guess you didn't exactly answer the question 7. You just counted severity but wasn't you supposed to find out how many Fatal, Serious and Slight accidents were for example when people are Commuting to/from work? Maybe this query would answer it better: SELECT v.JourneyPurpose, a.Severity, COUNT(a.AccidentIndex) AS Accidents_Count FROM Car_Accidents..accident AS a JOIN Car_Accidents..vehicle AS v ON a.AccidentIndex = v.AccidentIndex GROUP BY v.JourneyPurpose, a.Severity ORDER BY 2 ASC, 3 DESC
Thank you for giving free knowledge to the world
It's my pleasure
Is a good SQL tutorial for beginner. thank you
definitely
i like watching you solve the questions, more videos please xoxo from california
sure. thanks for your watching
question 5
easy Understanding Query
select WeatherConditions , count(WeatherConditions) as TotalAccident
from accident
where Severity = 'Fatal'
group by WeatherConditions
order by TotalAccident desc
thanks bro
thanks for your explanation.
Best video on topic
Thank you so much.
Very use full project Dear
thank you
Thank you for all your efforts on the channel.
Thank you for doing such a great tutorial 🎉
It's my pleasure
Fantastic Work Boss.
Thanks
Which symbol did you put beside the WHERE function and Severity variable to make them inactive?
you are just amazing.!! thanks
Thanks
Hey, nice tutorial, I am doing this for my portfolio project. DO I need to save the results of queries as well? Or just codes will be enough?
No need to save the query since all queries are stored in a log.
I just found your works. 👍
Thanks
hi ;
in question 4 ; can we use this query
SELECT
AgeGroup,
COUNT([AccidentIndex]) AS 'Total Accident',
AVG([AgeVehicle]) AS 'Average Year',
CASE
WHEN [AgeVehicle] BETWEEN 0 AND 5 THEN 'New'
WHEN [AgeVehicle] BETWEEN 6 AND 10 THEN 'Regular'
ELSE 'Old'
END AS AgeGroup
GROUP BY
AgeGroup;
thanks
Yes. You can use any query as long the query works and shows the same result. Just use your creativity 👍
Used that one too, but include a group for nulls
Good job....
While i import the vehicle file in MySQL, it shows row imported failed due to data truncated for column accidentindex at row 1 and 1265.
Try Microsoft SQL Management Studio instead of MySQL
First of all, thank you for the video. I am new at this and was searching for a project to do to learn more and start building a resume. But I have encountered a problem while importing the flat file 'vehicle'. It says -
The given value 'No' of type String from the data source cannot be converted to type bit for Column 4 [LeftHand]. (Microsoft.Data.SqlClient)
Failed to convert parameter value from a String to a Boolean. (Microsoft.Data.SqlClient)
Edit- I got it
Did you use the correct version of SQL Server?
@@Mer_Degan yeah, I think I didn't check allow null variables for a column the first time I guess
what is the purpose of accidentIndex in this query?
we could use this variable as n unique (primary key) for accident identifier.
Thank you for doing this analysis, Also can you please let me know how can i download this data, i tried to download github links provided above but i don't have find the data sets
Hi. In this link there are two CSV files. you can download them and import in your SQL management application (MySQL or MS SQL). github.com/M3hrdad-Dehghan/SQL/tree/main/Exploratory_Data_Analysis_of_Vehicle_Accident_by_SQL
is it normal to have AccidentIndex with scientific values (Like 2.02E+10)?
Accident Index is indeed a label in terms of data type. However, it is important to note that the values displayed in a column may not always correspond to the data type. For example, even though Accident Index is shown as a number, it is actually a label that is a combination of letters and numbers. Therefore, it is highly recommended to change the data type accordingly. It is worth mentioning that in the video, the accident index appears to be a scientific number, but it is actually a label.☺
@@Mer_Degan
in my case about 110.00 rows of AccidentIndex data converted to scidentific value in the procces of importing data to sql server database. which makes almost impossibble to analyse the data because we can't relate vehicleID's and AccidentIndex. I trided to change data types but doesn't work. couldn't figure out a solution.
Bruh there are null values in column Age Vehicle of vehicle table its not even getting imported in my mysql workbench csv is a headache fr !! Help me out someone
I will export all data in an Excel file and I will upload it in my website. You can go there and download the dataset.
@@Mer_Degan That would help sir
do you have a data what u can practice too
hi. you can see my GitHub in the description of videos
ok thank you
@@Mer_Degan
The left-hand column in the vehicle table cannot be imported with the bit or tinyint datatype. Please note that I am using SQL Server Management Studio (SSMS) for my database
please make sure you don't change the data type of variables
Hey i have a small doubt the excel sheet contains dates in different formats and i am not sure how to change them into single format can you help me
thanks in advance.
Please send me an email and explain your problem
Data analyst always work with Excel data only can please clarify my doubt
Excel is an application that is used for storing structured data. However, there are other tools available for storing structured data, such as databases, Access, and others. Apart from structured data, there are semi-structured data types, such as websites, and non-structured data types, such as voice, text, and video, that have their own storage systems. The most important step in data management is to understand the data itself, while tools and techniques are secondary.
Do you have the source of the data?
Unfortunately, it was on my local system and after changing system it was gone
I want same data set
You can download it by going to the link in the description of video.
Why most youtuber are using Ms Ms SQL server....why not use oracle SQL tool
Maybe just because it's easier to walk through. However, in real work condition, it doesn't matter which database management tool is being used
@@Mer_Degan thanks for sharing guidance and knowledge
@@Ganeshay-09 it's my pleasure
is this good for resume , please tell me guys
yes sure. I tried to put most common syntax in this video
What generates such data in real life? I'm just asking to expand my knowledge
This data is sourced from the UK Traffic Data Agency and represents genuine information for a particular timeframe.
Can u share the dataset
Just use the link in the description. (GitHub)
I have a hard time understanding subquiry
In SQL, a subquery is a query within another query. It's like a small query nested inside a larger on. This part selects "AccidentIndex" and "AgeVehicle" from the vehicle table. It also creates a new column called "AgeGroup" which categorizes vehicles into 'New', 'Regular', or 'Old' based on their age.In summary, the subquery first categorizes vehicles by age, and then the main query calculates statistics based on these categories.
in question 7 i used this query :
WITH AccidentCounts AS (
SELECT
V.[JourneyPurpose],
COUNT(A.[Severity]) AS 'Total Accident'
FROM
[dbo].[accident] A
JOIN
[dbo].[vehicle] V ON A.[AccidentIndex] = V.[AccidentIndex]
GROUP BY
V.[JourneyPurpose]
)
SELECT
[JourneyPurpose],
[Total Accident],
CASE
WHEN [Total Accident] BETWEEN 0 AND 1000 THEN 'Low'
WHEN [Total Accident] BETWEEN 1001 AND 3000 THEN 'Moderate'
ELSE 'High'
END AS 'Level'
FROM
AccidentCounts
ORDER BY
[Total Accident] DESC;
Hey bro., Is this the one you told me about you are working on? Let me check it out then I'll give you my feedback. But I already know it's amazing
Hi Simon. It's just about the EDA project using T-SQL. You can find it a useful project for dealing with data to explore more about it. After running EDA on data, you are ready to use one of the BI tools to visualize data to gain more insights. By the way, I planned to run the E-2-E project, but I was tasked with a huge workload. So, I try to publish more videos on different topics.
I guess you didn't exactly answer the question 7.
You just counted severity but wasn't you supposed to find out how many Fatal, Serious and Slight accidents were for example when people are Commuting to/from work?
Maybe this query would answer it better:
SELECT v.JourneyPurpose, a.Severity, COUNT(a.AccidentIndex) AS Accidents_Count
FROM Car_Accidents..accident AS a
JOIN Car_Accidents..vehicle AS v
ON a.AccidentIndex = v.AccidentIndex
GROUP BY v.JourneyPurpose, a.Severity
ORDER BY 2 ASC, 3 DESC