SQL Query Query

Question

Hello Team,

Please guide me in the following -

ID	Location	CertifiedBy	CertifiedOn	Month	Year
1	Gujarat	Riddhi.Valecha	14-03-2023 14:07	4	2023
2	Karnataka	Riddhi.Valecha	14-04-2023 14:27	4	2023
3	Haryana	Riddhi.Valecha	14-04-2023 14:35	4	2023
4	Gujarat	Riddhi.Valecha	14-03-2023 14:07	4	2023
5	Gujarat	Riddhi.Valecha	14-04-2023 14:27	4	2023
6	Gujarat	Riddhi.Valecha	14-04-2023 14:35	4	2023
7	Gujarat	Riddhi.Valecha	14-03-2023 14:07	4	2023
8	Karnataka	Riddhi.Valecha	14-04-2023 14:27	4	2023
9	Haryana	Riddhi.Valecha	14-04-2023 14:35	4	2023
10	Gujarat	Riddhi.Valecha	14-03-2023 14:07	4	2023
11	Karnataka	Riddhi.Valecha	14-04-2023 14:27	4	2023
12	Haryana	Riddhi.Valecha	14-04-2023 14:35	4	2023

I need the output as below -

Stock Location	Stock Owner	Date : DD/MMM/YYYY HH:MM
Stock Location	Stock Owner	Total Stock
Gujarat	Riddhi Valecha	6
Karnataka	Riddhi Valecha	3
Haryana	Riddhi Valecha	3

Please help me out in making the query and making this in GridView in Front-End.

Jignesh Kumar · Answer

Hello Ridhi, Please try this one, SELECT Location AS "Stock Location", CertifiedBy AS "Stock Owner", cast(CertifiedOn as date) AS "Date : DD/MMM/YYYY HH:MM", COUNT(*) AS "Total Stock" FROM Certifications GROUP BY Location, CertifiedBy,cast(CertifiedOn as date);

Tuhin Paul · Answer

In my above code Replace "your_connection_string_here" with your SQL Server connection string, and "your_query_here" with the SQL query from above. Replace "yourGridView" with the ID of your GridView control.

Tuhin Paul · Answer

Assuming you have a SQL Server database table named "Certifications" with the columns "ID", "Location", "CertifiedBy", "CertifiedOn", "Month", and "Year", you can use the following SQL query: SELECT Location AS 'Stock Location', CertifiedBy AS 'Stock Owner', CONVERT(varchar(16), CertifiedOn, 106) + ' ' + CONVERT(varchar(5), CertifiedOn, 108) AS 'Date : DD/MMM/YYYY HH:MM', COUNT(*) AS 'Total Stock' FROM Certifications WHERE Month = 4 AND Year = 2023 GROUP BY Location, CertifiedBy, CertifiedOn This query will group the certifications by location, certified by, and certified on date and time, and count the number of certifications for each group. The WHERE clause filters the results to only include certifications from April 2023. To display the query results in a GridView in the front-end, you can bind the GridView to a DataTable filled with the query results. using (SqlConnection connection = new SqlConnection("your_connection_string_here")) { SqlCommand command = new SqlCommand("your_query_here", connection); DataTable table = new DataTable(); SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(table); yourGridView.DataSource = table; yourGridView.DataBind(); }

Amit Mohanty · Answer

Try this SELECT Location AS 'Stock Location', CertifiedBy AS 'Stock Owner', CONVERT(VARCHAR(17), CertifiedOn, 113) AS 'Date : DD/MMM/YYYY HH:MM', COUNT(*) AS 'Total Stock' FROM YourTableNameHere GROUP BY Location, CertifiedBy, CertifiedOn ORDER BY Location;

Naimish Makwana · Answer

Use below query. SELECT Location AS "Stock Location", CertifiedBy AS "Stock Owner", DATE_FORMAT(CertifiedOn, '%d/%b/%Y %H:%i') AS "Date : DD/MMM/YYYY HH:MM", COUNT(*) AS "Total Stock" FROM Certifications GROUP BY Location, CertifiedBy; Thanks