GROUP AND COUNT - Relatively Simple Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP AND COUNT - Relatively Simple Query

Author

Topic

toniolo
Starting Member

14 Posts

Posted - 2007-11-30 : 04:56:14

Hi

I have a scenario with a query and would like to ask your assistance.

Actually this could be a very simple thing as it doesn’t involve any unusual or complex commands

I have 3 tables.

The table #1 contains information about the method the customers used to purchase my products. Only Electronic transactions are registered here (email, fax, Web)

The table #2 contains information about the customers, their accounts, names, addresses.

The table #3 contains information about ALL transactions with those customers, electronic and non electronic (via letter).

Table “Electronic”

Method Account #items Date
Web 12345 10 06/01
Email 65432 20 05/06
Fax 34567 20 06/10
Web 12345 40 06/11
Web 87655 17 06/12
Email 87655 23 06/12
Email 34567 12 06/13
Fax 65432 19 06/13

Table “Customers”

Account Name Address
12345 ABC Company 45 Oxford Ave.
87655 Norton Enterprises 322 Merrick Ave.
34567 Vincent Morris 22 Lake Road
65432 Douglas Industrial 64 Washington Sq

Table “All”

Method Account #items Date
Web 12345 10 06/01
Email 65432 20 05/06
Fax 34567 20 06/10
Web 12345 40 06/11
Web 87655 17 06/12
Email 87655 23 06/12
Email 34567 12 06/13
Fax 65432 19 06/13
Letter 12345 21 06/14
Email 12345 12 06/15
Letter 65432 11 06/17
Web 87655 09 06/17
Letter 34567 15 06/17

The idea is to run a query between certain range of dates (for example Between 06/01 and 06/20) and create a table below so I can GROUP and COUNT the number of items purchased per accounts using an electronic method, letter and also show the total number of items purchased regardless of the method (electronic or not).

Account Name Web Email Fax Letter Total
12345 ABC Company 50 12 0 21 83
65432 Douglas Industrial 0 20 19 11 50
34567 Vincent Morris 0 12 20 15 47
87655 Norton Enterprises26 23 0 0 49

I know that this is query that involves GROUPS, COUNT and JOINS but I haven’t found the proper way do this yet.

Thanks in advance for any suggestion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-30 : 05:25:43

SELECT c.Account,c.AccountName,wb.Web,e.Email,f.Fax,lt.Letter,wb.web + e.Email+f.Fax+lt.Letter AS 'Total'
FROM Customers c
CROSS APPLY(SELECT SUM(#items) AS 'Web' FROM All WHERE Account=c.Account AND Method='Web' AND Date BETWEEN '06/01' and '06/20')wb
CROSS APPLY(SELECT SUM(#items) AS 'Email' FROM All WHERE Account=c.Account AND Method='Email' AND Date BETWEEN '06/01' and '06/20')e
CROSS APPLY(SELECT SUM(#items) AS 'Fax' FROM All WHERE Account=c.Account AND Method='Fax' AND Date BETWEEN '06/01' and '06/20')f
CROSS APPLY(SELECT SUM(#items) AS 'Letter' FROM All WHERE Account=c.Account AND Method='Letter' AND Date BETWEEN '06/01' and '06/20')lt

kutumbarao
Starting Member

13 Posts

Posted - 2007-11-30 : 05:30:48

Hi,

We need to check ISNULL of ZERO other wise Total will give "NULL"

SELECT c.Account,c.Name,ISNULL(wb.Web,0) AS Web,ISNULL(e.Email,0) AS Email,ISNULL(f.Fax,0) AS Fax,ISNULL(lt.Letter,0) AS Letter,( ISNULL(wb.web,0) + ISNULL(e.Email,0) + ISNULL(f.Fax,0) + ISNULL(lt.Letter,0)) AS 'Total'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-30 : 05:49:04

yes kutumbarao thats true. good spot!

kutumbarao
Starting Member

13 Posts

Posted - 2007-11-30 : 06:11:13

Hi,

How about this

SELECT Account, [Name],
SUM(CASE WHEN Method = 'Web' THEN #items ELSE 0 END) AS 'Web',
SUM(CASE WHEN Method = 'Email' THEN #items ELSE 0 END) AS 'Email',
SUM(CASE WHEN Method = 'Fax' THEN #items ELSE 0 END) AS 'Fax',
SUM(CASE WHEN Method = 'Letter' THEN #items ELSE 0 END) AS 'Letter',
SUM(#items) AS Total
FROM
(SELECT c.Account,c.[Name], Method, SUM(#items) #items
FROM #Customers C
LEFT JOIN #All A ON A.Account = C.Account
WHERE CONVERT(DATETIME, '01/' + Date) BETWEEN CONVERT(DATETIME, '01/' + '06/01') and CONVERT(DATETIME, '01/' + '06/20')
GROUP BY C.Account, C.[Name], Method) X
GROUP BY Account, [Name]

ranganath
Posting Yak Master

209 Posts

Posted - 2007-11-30 : 06:35:08

Hi

try this --with Dynamically

CREATE TABLE #temp (ID INT, Names varchar(1000), Val INT)
INSERT INTO #TEmp
SELECT 100, 'Web',10 UNION ALL
SELECT 100,'Email',20 UNION ALL
SELECT 100,'Fax',30 UNION ALL
SELECT 101,'Web',10 UNION ALL
SELECT 101,'Letter',20

SELECT * FROM #temp

DECLARE @sql VARCHAR(MAX), @str varchar(Max)
SET @sql = ''
SET @str = ''

Select @sql = @sql + ', Min(Case when Names = ''' +Names + ''' Then Val End ) AS "' + Names + '"'
From (Select distinct Names From #Temp)a
--SELECT @sql

Select @str = @str + 'Select Id '+@sql+' From #Temp Group By Id'
print @str
Exec (@str)

DROP TABLE #Temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources