SQL උදව්වක් දෙන්ට පීස්

[lankanatha]

select කුඩු, පොලිස්, max(ඒකක) from (select කුඩු, පොලිස්, sum(ඒකක) as ඒකක, ව්සර from table group by කුඩු, පොලිස්, ව්සර ) where ව්සර = 2017 group by කුඩු, පොලිස්;

Did u try this

 

[gepa]

Did u try this

ekat hari giye nane machan

 

[dasith077]

select කුඩු, පොලිස්, max(ඒකක) from (select කුඩු, පොලිස්, sum(ඒකක) as ඒකක, ව්සර from table group by කුඩු, පොලිස්, ව්සර ) where ව්සර = 2017 group by කුඩු, පොලිස්;

okema agata " order by ඒකක desc limit 1;" dala try karapan

 

[lankanatha]

ekat hari giye nane machan

ai mokakdda wenne, out put eka daapan

 

[හෙනයා]

Select polis, kudu, kg ...... Group by (polis, kudu)

Oken enawa neh ekama jaathiye (polis kudu) ekathu wela kg tika sum wela..
Me widihata..

Gunadasa ganja 15+32
Gunadasa keralaganja 8
Piyadasa keralaganja 8+24
Piyadasa hashish 11

Ita passe oken wadi eka adala ganna beriweida?

Answer eka nemei.. podi idea ekak dunne

 

[kolavari]

SELECT *
FROM (
SELECT kudu, police, SUM(unit) AS TotSum
FROM KuduTable
WHERE YEAR = '2017'
GROUP BY kudu, police
ORDER BY TotSum DESC) T
GROUP BY kudu

subquery ekak nathuwa ganna puluwannam hodai..

Sinhala version

SELECT *
FROM (
SELECT කුඩු, පොලිස්, SUM(එකක) AS TotSum
FROM KuduTable
WHERE YEAR = '2017'
GROUP BY කුඩු, පොලිස්
ORDER BY TotSum DESC) T
GROUP BY කුඩු;

 

[nalaka526]

උත්තරේ හරිද බලන්න

SELECT M.Kudu, M.Polis, M.Ekaka, M.Wasara FROM ( 
	SELECT Kudu, MAX(Ekaka) Ekaka
	FROM (
		SELECT Kudu, SUM(Ekaka) Ekaka
		FROM SriLankaPolis 
		WHERE Wasara=2017
		GROUP BY Kudu, Polis, Wasara
	) D 
	GROUP BY Kudu) P
        INNER JOIN 
	(	SELECT Polis, Kudu, SUM(Ekaka) Ekaka, Wasara
		FROM SriLankaPolis 
		WHERE Wasara=2017
		GROUP BY Kudu, Polis, Wasara
	) M 
	ON M.Kudu = P.Kudu AND M.Ekaka = P.ekaka 
ORDER BY Kudu

 

[~Pirate~]

ෆෝන් එකෙන් බන් හිටියේ උදේ.දැනුයි ගෙදර අවේ.
බලපන් මේක


CREATE TABLE PoliceDrugCatch
(
ID INT IDENTITY PRIMARY KEY,
OfficerName VARCHAR(100),
DrugName VARCHAR(50),
Amount INT,
[YEAR] INT
)
--DROP TABLE PoliceDrugCatch
--DROP TABLE #PoliceDrugCatch
INSERT INTO PoliceDrugCatch(OfficerName,DrugName,Amount,[YEAR]) VALUES('Gunadasa','Ganja',15,2017)
INSERT INTO PoliceDrugCatch(OfficerName,DrugName,Amount,[YEAR]) VALUES('SiriDasa','Abin',9,2017)
INSERT INTO PoliceDrugCatch(OfficerName,DrugName,Amount,[YEAR]) VALUES('Piyadasa','Hashis',11,2017)
INSERT INTO PoliceDrugCatch(OfficerName,DrugName,Amount,[YEAR]) VALUES('Saman','Ganja',7,2017)
INSERT INTO PoliceDrugCatch(OfficerName,DrugName,Amount,[YEAR]) VALUES('Saman','Hashis',20,2017)

CREATE TABLE #PoliceDrugCatchTemp
(
OfficerName VARCHAR(100),
DrugName VARCHAR(50),
Amount INT,
)

INSERT INTO #PoliceDrugCatchTemp
SELECT P.OfficerName,P.DrugName,SUM(P.Amount)
FROM PoliceDrugCatch P
WHERE P.[YEAR]=2017
GROUP BY P.OfficerName,P.DrugName

SELECT P.OfficerName,P.DrugName,C.AMT FROM
#PoliceDrugCatchTemp P
INNER JOIN(
SELECT MAX(Amount) AS AMT
FROM #PoliceDrugCatchTemp
WHERE DrugName='Ganja'
) C ON P.Amount = C.AMT

UNION

SELECT P.OfficerName,P.DrugName,C.AMT FROM
#PoliceDrugCatchTemp P
INNER JOIN(
SELECT MAX(Amount) AS AMT
FROM #PoliceDrugCatchTemp
WHERE DrugName='Abin'
) C ON P.Amount = C.AMT

UNION

SELECT P.OfficerName,P.DrugName,C.AMT FROM
#PoliceDrugCatchTemp P
INNER JOIN(
SELECT MAX(Amount) AS AMT
FROM #PoliceDrugCatchTemp
WHERE DrugName='Hashis'
) C ON P.Amount = C.AMT

 

[~Pirate~]

ko ban hari giyada

 

[gepa]

ko ban hari giyada

appata siri.. rachanawak thibe..int karala balannam boss

 

[nalaka526]

මේක වැරදිද?

උත්තරේ හරිද බලන්න

SELECT M.Kudu, M.Polis, M.Ekaka, M.Wasara FROM ( 
	SELECT Kudu, MAX(Ekaka) Ekaka
	FROM (
		SELECT Kudu, SUM(Ekaka) Ekaka
		FROM SriLankaPolis 
		WHERE Wasara=2017
		GROUP BY Kudu, Polis, Wasara
	) D 
	GROUP BY Kudu) P
        INNER JOIN 
	(	SELECT Polis, Kudu, SUM(Ekaka) Ekaka, Wasara
		FROM SriLankaPolis 
		WHERE Wasara=2017
		GROUP BY Kudu, Polis, Wasara
	) M 
	ON M.Kudu = P.Kudu AND M.Ekaka = P.ekaka 
ORDER BY Kudu