SELECT a.Name,
a.id,
a.Type,
a.date,
COUNT(b.pac),
a.ConName
FROM TableA a
JOIN TableB b ON a.Taskgenid = b.Taskgenid
AND a.formgenid = b.formgenid
JOIN TableC c ON A.id = c.ID
JOIN TableD d ON D.Typeid = c.Typeid
WHERE a.date BETWEEN @Begindate AND @Enddate
AND a.type IN (1, 2, 3)
GROUP BY a.Name,
a.id,
a.Type,
a.date,
a.ConName
I doesnt want to group the columns a.id,a.type,a.date,a.conname. If i remove those columns it showing error that a.id, a.type,a.date,a.conname is not group by or aggregate.
I want only Count(b.pac)
Loading

Ravi ShekharPosted Jul 17, 2013, 6:01 AM
Iftikar HussainPosted Jul 17, 2013, 2:54 AM
Regards,
Iftikar
Santosh KumarPosted Jul 17, 2013, 2:39 AM
Vishwas KadamannayaPosted Jul 17, 2013, 2:30 AM
First choose what u need in SELECT statement and then group only that columns,
SELECT a.Name,
COUNT(b.pac)
FROM TableA a
JOIN TableB b ON a.Taskgenid = b.Taskgenid
AND a.formgenid = b.formgenid
JOIN TableC c ON A.id = c.ID
JOIN TableD d ON D.Typeid = c.Typeid
WHERE a.date BETWEEN @Begindate AND @Enddate
AND a.type IN (1, 2, 3)
GROUP BY a.Name
Santosh KumarPosted Jul 17, 2013, 2:23 AM
Iftikar HussainPosted Jul 16, 2013, 11:49 AM
If you don't require those in output column then you can remove from select list otherwise you have to use join for same query
Regards,
Iftikar
Hemant SrivastavaPosted Jul 16, 2013, 11:16 AM
Because I ran almost same query and got the result as below:
SELECT
COUNT(LOCATION.LOC_ID_NR) AS COUNT_OF_LOC_IDs
FROM [TADDRS] AS ADDRESS
INNER JOIN TLOCATN AS LOCATION ON LOCATION.AD_SYS_NR = ADDRESS.AD_SYS_NR
WHERE ADDRESS.CNY_CD = 'US'
GROUP BY ADDRESS.AD_POL_DIV_2_NA, ADDRESS.AD_POL_DIV_2_NA, ADDRESS.AD_PSL_1_CD, ADDRESS.CNY_CD