jon jon - 2 months ago 9
SQL Question

Please help me to find the mistake in query

Hye,

I have table AddClient which have data like this

idClient NameClient Balance Date

CL-MK Muhammad Kashif CL-MK 9000 2016-10-01
CL-MA Asim CL-MA 8000 2016-10-01
CL-MQ Qasim CL-MQ 7000 2016-10-01
CL-MS Saim CL-MS 1000 2016-10-01
CL-MF Fahad CL-MF 3000 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MA Asim CL-MA 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MK Muhammad Kashif CL-MK 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01
CL-MS Saim CL-MS 2016-10-01
CL-MK Muhammad Kashif CL-MK 409005 2016-10-01
CL-MK Muhammad Kashif CL-MK 400000 2016-10-01
CL-MA Asim CL-MA 566340 2016-10-01
CL-MA Asim CL-MA 560000 2016-10-01
CL-MS Saim CL-MS 401005 2016-10-01
CL-MS Saim CL-MS 400000 2016-10-01
CL-MF Fahad CL-MF 2016-10-01
CL-MQ Qasim CL-MQ 2016-10-01


In the Balance column the blank space is equal to null

I am Trying this query to get the minimum balance of every client

SELECT idClient,NameClient,min(Balance) from AddClient group by NameClient


But Result show like this

CL-MA Asim CL-MA 560000
CL-MF Fahad CL-MF 3000
CL-MK Muhammad Kashif CL-MK 400000
CL-MQ Qasim CL-MQ 7000
CL-MS Saim CL-MS 1000


in Client name Asim and Kashif not show correct result
the minimum balance of Asim is
8000
and Kashif is
9000


where is the mistake?

Answer

You need to group both by ID and name of the client instead of grouping only by name:

SELECT idClient, NameClient, min(Balance) 
FROM AddClient 
GROUP BY idClient, NameClient

Additionally, it seems that the data type that you are using for balance is not a numeric type - that would explain the strange results you are seeing. If the data type is not numeric, you should convert it to numeric type first. Below code assumes that all values in Balance column can be converted to integer values, otherwise you will get an exception:

SELECT idClient, NameClient, min(CAST(Balance as INTEGER)) 
FROM AddClient 
GROUP BY idClient, NameClient