Thanks for this. I finally have PIVOT working! I've converted two stored procedures so far and will be converting more now that I understand how it works.
so at @2:10 you want to only select specific columns that they asked for which was the ecom quantity and store quantity don't need to see the color and all that
SQL is supposed to be simple human readable. Always had issues with the Pivot, the fact that the Categories values had to be hard-coded in the code makes it static. There are solutions on the internet how to first read the list of categories values in a variable.
So what's the value in using OuterApply or CrossApply? Based on the demo, the result could be achieved with Table Joins. Anyway, I can Google for the answer. Was always curious when to use Cross Apply or Outer Apply, never knew the answer until this came up in a job interview.
Cool, i have a doubt. Suppose i have a table with 3 columns say name,id,address and say 100 records. The table has some of the values in name,address filled with null or blank space. I Need to take report which shows me the count of records with blank spaces,null and this shud be achieved without using group by
how can you import csv file to an already existing table using sql query? like suppose you have Customer table and now you want to add data from xyz.csv file and both Customer and csv file have same attribute ,then how to do such task?
Neat and concise. Great for familiarizing oneself with these features. If it included the UNPIVOT statement it would a make for a nice, complete tutorial.
Thanks a lot....I am working on data analysis and i have to use sql with advance functions for transformations etc and so I thought that u could help me....thanks ...
Hello Mr. Oliver! Could you take a doubt please? Regarding the part of the query before the pivot? I'm using the same base as you and your query, I achieved exactly the same results that you showed in the video. select c.name Category, count (p.productid) totalproducts from production.product p inner join production.productcategory c on c.productcategoryid = p.productsubcategoryid group by c.name go Accessories 8 Bikes 32 Clothing 22 Components 43 It's a simple query like you said then I tried to take the test and I couldn't reach the number that the query arrived I'll take as an example the accessories category (productcategoryid = 4) that gave a total of 8: select * from Production.ProductSubcategory where ProductCategoryID = 4 go productsubcategoryid productcategoryid 26 4 27 4 28 4 29 4 30 4 31 4 32 4 33 4 34 4 35 4 36 4 37 4 Ok, after this result I did this search: select count(productid) count_products_cat_accessories from Production.Product where ProductSubcategoryID between 26 and 37 count_products_cat_accessories 29 If possible, can you show me why I didn't get the result of 8? Thank you!!
I would have to go back and check but I use this database for other demos/purposes and it is possible I have modified the underlying tables. It has been a little while so would have to check to be sure but that is my guess. Thanks for watching.
@@JamesOliver I thank you! Your videos helped me a lot, they are very didactic! Ok, I understood what you said about the db but in this case, your query returned exactly the same results as the categories shown in your video. I believe that the bases are hitting. In the future, if you go back to the original DB AdventureWorks2012, I would appreciate it if you can check where I went wrong. Follow the test print link. drive.google.com/file/d/1In0tEldKrakm8YM01qRo_5n5T1or1jXR/view?usp=sharing My best regards !
What a legen! His restaurants went bankrupt but now he teaches us SQL. Thank you Jamie Oliver
LOL ;)
I find your vids real helpful as a full stack dev tweaking sprocs every now and then. Thanks for the content.
Thanks for this. I finally have PIVOT working! I've converted two stored procedures so far and will be converting more now that I understand how it works.
Great Video. Thanks for making your series on SQL... I've learned a ton from your clear and concise explanations.
Thank you! I was looking for the SQL equivalent of the || operator, and had read about coalesce before. Your video really made it "click" for me.
Always like to know I'm helping people. That is what it is all about.
>Thanks for watching
Thanks for creating
Amazing ! finally could understand coalesce and pivot. Please make videos on stored procedures, CTE and materialised views.
so at @2:10 you want to only select specific columns that they asked for which was the ecom quantity and store quantity don't need to see the color and all that
Thanks for this. If I had a variable amount of categories, how could I pivot?
Great vid! Difficult topics explained in a very clear and easy way to understand! Thanks and keep them coming!
Thank you so much! Will do! :)
Loved the video!!! thank you for making this amazing video.
Man you just rock thank you for sharing ❤
Thank you! Great video!
@3:17 is there a is not null for the product id or is that a different concept?
at the first query, isn't it the same as union all? instead of left join+coalesce? if not, when do you use which?
Nice breakdown of pivot table
Great video!
This is super useful stuff , thanks a lot mate
Thanks bro... Amazing video
Excellent tutorial
Neo-1 I appreciate the kind words
How to enable viewing syntax and command definition [while typing any command] in MS-SQL as we see in Excel
loved it :)
In the pivot statement, would it have been possible to select distinct values rather than listing them?
You need to reference the actual column names. A select returns the values. Unless I'm misunderstanding the question.
@@JamesOliver i guess he meant making a subquery with (select distinct(c.name or category) from productcategory). can we do that in here?
SQL is supposed to be simple human readable. Always had issues with the Pivot, the fact that the Categories values had to be hard-coded in the code makes it static. There are solutions on the internet how to first read the list of categories values in a variable.
Hi, We may also use Isnull(s.ProductID, e.ProductID) ProductID instead of coalesce this gives also same output. plz verify.
Brilliant! Well done!
Great video.
Appreciate it very much. :)
Thanks for the video. Can you please make a video on how Enterprise manager reports can be leveraged.
Great suggestion! I will put that on my list.
how can i find to this database?
So what's the value in using OuterApply or CrossApply? Based on the demo, the result could be achieved with Table Joins. Anyway, I can Google for the answer. Was always curious when to use Cross Apply or Outer Apply, never knew the answer until this came up in a job interview.
Cool, i have a doubt. Suppose i have a table with 3 columns say name,id,address and say 100 records. The table has some of the values in name,address filled with null or blank space. I Need to take report which shows me the count of records with blank spaces,null and this shud be achieved without using group by
It's rubbish that you have to hard code the columns for the pivot... can you not do a select Distinct to populate that?
Can you prepare basic to advanced series, so every one can learn SQL
how can you import csv file to an already existing table using sql query?
like suppose you have Customer table and now you want to add data from xyz.csv file and both Customer and csv file have same attribute ,then how to do such task?
Neat and concise. Great for familiarizing oneself with these features. If it included the UNPIVOT statement it would a make for a nice, complete tutorial.
hi your advanced sql session is really helpful...could you please upload some more advanced queries?
ADITYA ANAND sure will do
Thanks a lot....I am working on data analysis and i have to use sql with advance functions for transformations etc and so I thought that u could help me....thanks ...
Great videos. Thank you. Only if you can control those juicy tong sounds.
Thank you
@2:45 I guess they don't like to see nulls in their data?
Yea just quickly handling the NULLs and converting to zero
This was a helpful explanation of PIVOT
Glad you liked. :)
Not sure if you will ever respond but.. why you didn't used the alias for ProductId in the COUNT function?
Why can't we just go for a simple subquery for the 1st requirement , Select * from table1 t1 where t1.id in (select id from table2)
Hello Mr. Oliver!
Could you take a doubt please?
Regarding the part of the query before the pivot?
I'm using the same base as you and your query, I achieved exactly the same results that you showed in the video.
select c.name Category,
count (p.productid) totalproducts
from production.product p
inner join production.productcategory c
on c.productcategoryid = p.productsubcategoryid
group by c.name
go
Accessories 8
Bikes 32
Clothing 22
Components 43
It's a simple query like you said then I tried to take the test and I couldn't reach the number that the query arrived
I'll take as an example the accessories category (productcategoryid = 4) that gave a total of 8:
select * from Production.ProductSubcategory
where ProductCategoryID = 4
go
productsubcategoryid productcategoryid
26 4
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 4
36 4
37 4
Ok, after this result I did this search:
select
count(productid) count_products_cat_accessories from Production.Product
where ProductSubcategoryID between 26 and 37
count_products_cat_accessories
29
If possible, can you show me why I didn't get the result of 8?
Thank you!!
I would have to go back and check but I use this database for other demos/purposes and it is possible I have modified the underlying tables. It has been a little while so would have to check to be sure but that is my guess. Thanks for watching.
@@JamesOliver I thank you! Your videos helped me a lot, they are very didactic!
Ok, I understood what you said about the db but
in this case, your query returned exactly the same results as the categories shown in your video.
I believe that the bases are hitting.
In the future, if you go back to the original DB AdventureWorks2012, I would appreciate it if you can check where I went wrong. Follow the test print link.
drive.google.com/file/d/1In0tEldKrakm8YM01qRo_5n5T1or1jXR/view?usp=sharing
My best regards
!
It would be hella cool if you add timestamps
If I had u as my datasci professor I couldve been working at IBM by now
Midnight Run :)
Will I be able to apply this on mysql??
appreciate the vid brother
what application are you using sir ?
Sql server express and management studio. All free.
How un earth is this considered advanced
Nice!!
Thx
lost me @4:30 what is this "function"
Ayejax just a function that returns rows
How are you.
should be titled TSQL