Help with COUNT query - SQL Server Forums

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)

Help with COUNT query

Author

Topic

LeeNewman
Starting Member

16 Posts

Posted - 2009-03-11 : 04:19:51

Hi

Can anyone here help me with a problem I am strggling with?

I want to know how many times ALL the numbers in tbl_table1.ID appear in tbl_table2.col1 OR tbl_table2.col2 OR tbl_table2.col3 AND tbl_table2.cond1 = 'true' AND tbl_table2.cond2 = 'false' ORDER BY Total DESC. Also include tbl_table1.Name in the results table.


tbl_table1     ID      Name
               1       Test1
               2       Test2
               3       Test3
               4       Test4
               5       Test4

tbl_table2    col1     col2     col3     cond1      cond2
               1         3        5      true       true
               4         2        1      true       false
               3         1        2      false      true
               5         1        3      false      true
               2         4        1      true       false
               1         5        4      true       true
               1         5        2      false      false
               3         1        4      true       false

So my results will look like this:

ID      Name       Total
1       Test1        3
4       Test4        3
2       Test2        2
3       Test3        1
5       Test5        0

Many Thanks

Lee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 05:03:52

[code]-- Prepare sample data
DECLARE @Table1 TABLE
(
ID INT,
Name VARCHAR(20)
)

INSERT @Table1
SELECT 1, 'Test1' UNION ALL
SELECT 2, 'Test2' UNION ALL
SELECT 3, 'Test3' UNION ALL
SELECT 4, 'Test4' UNION ALL
SELECT 5, 'Test5'

DECLARE @Table2 TABLE
(
col1 INT,
col2 INT,
col3 INT,
cond1 BIT,
cond2 BIT
)

INSERT @Table2
SELECT 1, 3, 5, 1, 1 UNION ALL
SELECT 4, 2, 1, 1, 0 UNION ALL
SELECT 3, 1, 2, 0, 1 UNION ALL
SELECT 5, 1, 3, 0, 1 UNION ALL
SELECT 2, 4, 1, 1, 0 UNION ALL
SELECT 1, 5, 4, 1, 1 UNION ALL
SELECT 1, 5, 2, 0, 0 UNION ALL
SELECT 3, 1, 4, 1, 0

-- Display the result
SELECT t1.ID,
t1.Name,
COUNT(t2.theValue) AS Total
FROM @Table1 AS t1
LEFT JOIN (
SELECT u.theValue
FROM @Table2 AS t
UNPIVOT (
theValue
FOR theCol IN (t.Col1, t.Col2, t.Col3)
) AS u
WHERE u.cond1 = 1
AND u.cond2 = 0
) AS t2 ON t2.theValue = t1.ID
GROUP BY t1.ID,
t1.Name
ORDER BY COUNT(t2.theValue) DESC,
ID[/code]

E 12°55'05.63"
N 56°04'39.26"

matty
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 05:08:46

SELECT t.Id,t.Name,SUM(CASE WHEN t.Id = t1.col1 OR t.Id= t1.col2 OR t.Id =t1.col3 THEN 1 ELSE 0 END) AS Total
FROM tbl_table1 t,
(
SELECT * FROM tbl_table2 WHERE cond1 ='true' AND cond2 = 'false'
) t1
GROUP BY Id,Name
ORDER BY Total DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 05:11:37

Add this sample data and try again.

SELECT	1, 2, 2, 1, 0 UNION ALL

E 12°55'05.63"
N 56°04'39.26"

LeeNewman
Starting Member

16 Posts

Posted - 2009-03-11 : 14:30:56

Hi guys

It's amazing how many people I have asked this same question for months and no has been able to answer it?? I thought it must be such a simple query and people must need to do it all the time. I just couldn't understand why anyone who has written a SQL query wouldn't be able to answer it. Anyway, I popped down the library and got the SQL for Dummies book, found the solution straight away. I do very much appreciate your time to help however!

Peso - I have thousands of columns! Come on, I'm not going to select each and every one of them.

Matty - I could see where you were coming from (apart from SUM CASE and WHEN????) and everyone I have asked in the past has come up with a similar answer.

The correct simple answer to a simple question is:


SELECT
    tbl_table1.ID,
    tbl_table1.Name, 
    COUNT (tbl_table1.ID) AS Total

FROM
    tbl_table1, tbl_table2

WHERE (
    tbl_table1.ID = tbl_table2.col1 OR
    tbl_table1.ID = tbl_table2.col2 OR
    tbl_table1.ID = tbl_table2.col3)
AND
    tbl_table2.cond1 = 'True' AND
    tbl_table2.cond2 = 'False'

GROUP BY
    tbl_table1.ID, 
    tbl_table1.Name

ORDER BY 
    Total DESC;

I hope this helps someone else.

Many thanks everyone

Lee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 04:01:01

You missed the PREPARE SAMPLE DATA part?
Skip that one and concentrate on the SELECT part (DISPLAY THE RESULT) which give you the correct answer, efficiently.

I also tried with your original sample data and expected output, and I am sorry to inform you that your suggestion does not return the Test5 record!
And that's because you don't realize how your tables are joined.

See this example

-- Prepare sample data
DECLARE	@Table1 TABLE
	(
		ID INT,
		Name VARCHAR(20)
	)

INSERT	@Table1
SELECT	1, 'Test1' UNION ALL
SELECT	2, 'Test2' UNION ALL
SELECT	3, 'Test3' UNION ALL
SELECT	4, 'Test4' UNION ALL
SELECT	5, 'Test5'

DECLARE	@Table2 TABLE
	(
		col1 INT,
		col2 INT,
		col3 INT,
		cond1 BIT,
		cond2 BIT
	)

INSERT	@Table2
SELECT	1, 3, 5, 1, 1 UNION ALL
SELECT	4, 2, 1, 1, 0 UNION ALL
SELECT	3, 1, 2, 0, 1 UNION ALL
SELECT	5, 1, 3, 0, 1 UNION ALL
SELECT	2, 4, 1, 1, 0 UNION ALL
SELECT	1, 5, 4, 1, 1 UNION ALL
SELECT	1, 5, 2, 0, 0 UNION ALL
SELECT	3, 1, 4, 1, 0

-- Display the result
SELECT		t1.ID,
		t1.Name,
		COUNT(t2.theValue) AS Total
FROM		@Table1 AS t1
LEFT JOIN	(
			SELECT		u.theValue
			FROM		@Table2 AS t
			UNPIVOT		(
						theValue
						FOR theCol IN (t.Col1, t.Col2, t.Col3)
					) AS u
			WHERE		u.cond1 = 1
					AND u.cond2 = 0
		) AS t2 ON t2.theValue = t1.ID
GROUP BY	t1.ID,
		t1.Name
ORDER BY	COUNT(t2.theValue) DESC,
		ID

-- matty
SELECT t.Id,t.Name,SUM(CASE WHEN t.Id = t1.col1 OR t.Id= t1.col2 OR t.Id =t1.col3 THEN 1 ELSE 0 END) AS Total
FROM @table1 t,
(
SELECT * FROM @table2 WHERE cond1 ='true' AND cond2 = 'false'
) t1
GROUP BY Id,Name
ORDER BY Total DESC

-- Lee
SELECT
    t1.ID,
    t1.Name, 
    COUNT (t1.ID) AS Total

FROM
    @table1 as t1, @table2 as t2

WHERE (
    t1.ID = t2.col1 OR
    t1.ID = t2.col2 OR
    t1.ID = t2.col3)
AND
    t2.cond1 = 'True' AND
    t2.cond2 = 'False'
GROUP BY
    t1.ID, 
    t1.Name
ORDER BY 
    Total DESC;

-- Peso
ID	Name	Total
1	Test1	3
4	Test4	3
2	Test2	2
3	Test3	1
5	Test5	0

-- Matty
Id	Name	Total
1	Test1	3
4	Test4	3
2	Test2	2
3	Test3	1
5	Test5	0

-- Lee
ID	Name	Total
1	Test1	3
4	Test4	3
2	Test2	2
3	Test3	1

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 04:02:41

And when I inlcude the extra sample data record posted 03/11/2009 : 05:11:37

SELECT	1, 2, 2, 1, 0 UNION ALL

I get these results from the three suggestions

-- Peso
ID	Name	Total
1	Test1	4
2	Test2	4
4	Test4	3
3	Test3	1
5	Test5	0

-- Matty
Id	Name	Total
1	Test1	4
2	Test2	3
4	Test4	3
3	Test3	1
5	Test5	0

-- Lee
ID	Name	Total
1	Test1	4
2	Test2	3
4	Test4	3
3	Test3	1

E 12°55'05.63"
N 56°04'39.26"

matty
Posting Yak Master

161 Posts

Posted - 2009-03-12 : 07:39:17

I have given a suggestion by trying with your original sample data and expected output.
Ofcourse, it fails when this sample data is included.
SELECT 1, 2, 2, 1, 0 UNION ALL

Your answer is simple, but it does not gives the expected output.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 07:54:46

Only OP can tell if there are two kind of same number in one record should count as two or count as one.
And besides, Lee's suggestion doesn't even present Test5 record in any case.

E 12°55'05.63"
N 56°04'39.26"

LeeNewman
Starting Member

16 Posts

Posted - 2009-03-12 : 18:27:14

Yeah thanks guys!

You are right and I am wrong! Sorry Peso because I didn't read your post properly in the first place and had I had done so, would have come back with a different response!

My sample did actually work for what I wanted it for and was aware that zero values were not returned which was fine as i only wanted TOP records to be returned. In any case, even though I don't fully understand it (I sort of understand what is going on, never seen the UNPIVOT though), I will use Peso's query because if I need to use anything similar in the future, I know it will work PROPERLY.

Thanks again!

Lee

LeeNewman
Starting Member

16 Posts

Posted - 2009-03-18 : 07:32:31

Hi

I have been looking at how I can take Peso's query a step further and have the total include all child totals as well. However, reading on various forums etc. I get the impression it's not possible (or at least very difficult) using my parent_id model. Apparently I should have used a nested sets model?? Is this correct?

Surely, if using Peso's query I am able to get all indivdual totals for all the categories there must be a way to get all child totals as well if I supply a starting point?

For example, let's say I am browsing category ID 1. Categories 23, 56, 103, 212 are children of category 1. Using Peso's query I am easily able to find out how many entries are in those categories. However, I want to know how many entries are in the child categories AND the child categories of those categories.

I'm guessing I need to make Peso's query a sub-query of a query that is able to move down the tree counting the totals??

My tbl_table1 includes a parent_id column and in fact even includes a parent_id2 and 3 columns so a child is able to find it's way home but I don't think that would help in this case as PID2 and 3 are not indicative of level directly, only who the parent is and it's parent.

Any help would be very much appreciated.

Thanks if you've read this far!

Lee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 08:10:12

No, you should use a recursive CTE.
For this sample data

-- Prepare sample data
DECLARE	@Table1 TABLE
	(
		ID INT,
		Name VARCHAR(20)
	)

INSERT	@Table1
SELECT	1, 'Test1' UNION ALL
SELECT	2, 'Test2' UNION ALL
SELECT	3, 'Test3' UNION ALL
SELECT	4, 'Test4' UNION ALL
SELECT	5, 'Test5'

DECLARE	@Table2 TABLE
	(
		col1 INT,
		col2 INT,
		col3 INT,
		cond1 BIT,
		cond2 BIT
	)

INSERT	@Table2
SELECT	1, 3, 5, 1, 1 UNION ALL
SELECT	4, 2, 1, 1, 0 UNION ALL
SELECT	3, 1, 2, 0, 1 UNION ALL
SELECT	5, 1, 3, 0, 1 UNION ALL
SELECT	2, 4, 1, 1, 0 UNION ALL
SELECT	1, 5, 4, 1, 1 UNION ALL
SELECT	1, 5, 2, 0, 0 UNION ALL
SELECT	3, 1, 4, 1, 0

what is your expected output and how does it look like?

E 12°55'05.63"
N 56°04'39.26"

LeeNewman
Starting Member

16 Posts

Posted - 2009-03-18 : 09:28:49

[code]
I would like to change the sample data if i may.

-- Prepare sample data
DECLARE @Table1 TABLE
(
ID INT,
Name VARCHAR(20),
PID INT
)

INSERT @Table1
SELECT 1, 'Test1', 4 UNION ALL
SELECT 2, 'Test2', NULL UNION ALL
SELECT 3, 'Test3', 6 UNION ALL
SELECT 4, 'Test4', 2 UNION ALL
SELECT 5, 'Test5', 3 UNION ALL
SELECT 6, 'Test6', 2 UNION ALL
SELECT 7, 'Test7', 5 UNION ALL
SELECT 8, 'Test8', 6 UNION ALL
SELECT 9, 'Test9', 2 UNION ALL

DECLARE @Table2 TABLE
(
col1 INT,
col2 INT,
col3 INT
)

INSERT @Table2
SELECT 1, 3, 5 UNION ALL
SELECT 4, 2, 6 UNION ALL
SELECT 3, 8, 2 UNION ALL
SELECT 5, 1, 3 UNION ALL
SELECT 7, 4, 1 UNION ALL
SELECT 1, 5, 4 UNION ALL
SELECT 8, 5, 2 UNION ALL
SELECT 9, 3, 7 UNION ALL
SELECT 8, 5, 1 UNION ALL
SELECT 3, 6, 9

-- Query
I want to know the sub-categories of category 2 and how many entries they have.

-- Expected output:

Name Total
Test4 8
Test6 10
Test9 2
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 09:37:58

All subcategories, or just the first ones?

ID	Name	PID
2	Test2	NULL
4	Test4	2
6	Test6	2
9	Test9	2 <-- Only down here or the rest too?
3	Test3	6
8	Test8	6
5	Test5	3
7	Test7	5
1	Test1	4

E 12°55'05.63"
N 56°04'39.26"

LeeNewman
Starting Member

16 Posts

Posted - 2009-03-18 : 09:43:31

I want the rest also.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 10:18:52

[code]DECLARE @Table1 TABLE
(
ID INT,
Name VARCHAR(20),
PID INT
)

INSERT @Table1
SELECT 1, 'Test1', 4 UNION ALL
SELECT 2, 'Test2', NULL UNION ALL
SELECT 3, 'Test3', 6 UNION ALL
SELECT 4, 'Test4', 2 UNION ALL
SELECT 5, 'Test5', 3 UNION ALL
SELECT 6, 'Test6', 2 UNION ALL
SELECT 7, 'Test7', 5 UNION ALL
SELECT 8, 'Test8', 6 UNION ALL
SELECT 9, 'Test9', 2

DECLARE @Table2 TABLE
(
col1 INT,
col2 INT,
col3 INT
)

INSERT @Table2
SELECT 1, 3, 5 UNION ALL
SELECT 4, 2, 6 UNION ALL
SELECT 3, 8, 2 UNION ALL
SELECT 5, 1, 3 UNION ALL
SELECT 7, 4, 1 UNION ALL
SELECT 1, 5, 4 UNION ALL
SELECT 8, 5, 2 UNION ALL
SELECT 9, 3, 7 UNION ALL
SELECT 8, 5, 1 UNION ALL
SELECT 3, 6, 9

;WITH Entries(theValue, Items)
AS (
SELECT theValue,
SUM(Items) AS Items
FROM (
SELECT u.theValue,
1 AS Items
FROM @Table2 AS t1
UNPIVOT (
theValue
FOR theCol IN (t1.col1, t1.col2, t1.col3)
) AS u

UNION ALL

SELECT ID,
0
FROM @Table1
) AS d
GROUP BY theValue
), Yak (ID, Name, PID, Items)
AS (
SELECT t1.ID,
t1.Name,
t1.PID,
e.Items
FROM @Table1 AS t1
INNER JOIN Entries AS e ON e.theValue = t1.ID
LEFT JOIN @Table1 AS x ON x.PID = t1.ID
WHERE x.PID IS NULL

UNION ALL

SELECT t1.ID,
t1.Name,
t1.PID,
y.Items + e.Items
FROM @Table1 AS t1
INNER JOIN Entries AS e ON e.theValue = t1.ID
INNER JOIN Yak AS y ON y.PID = t1.ID
), Ctrl (ID, PID, Pth)
AS (
SELECT ID,
PID,
CAST(ID AS VARCHAR(MAX)) AS Pth
FROM @Table1
WHERE ID = 2

UNION ALL

SELECT t1.ID,
t1.PID,
c.Pth + '>' + CAST(t1.ID AS VARCHAR(MAX))
FROM @Table1 AS t1
INNER JOIN Ctrl AS c ON c.ID = t1.PID
)

SELECT y.ID,
y.Name,
SUM(y.Items) AS Total
FROM Yak AS y
INNER JOIN Ctrl AS c ON c.ID = y.ID
GROUP BY y.ID,
y.Name,
c.Pth
ORDER BY c.Pth[/code]

E 12°55'05.63"
N 56°04'39.26"

LeeNewman
Starting Member

16 Posts

Posted - 2009-03-18 : 15:46:38

Thanks Peso.

I applied your query to my real live DB and I get all the correct totals for the very bottom categories but the higher categories (parents) seem to be out of count.

I don't want you to spend much more of your time on this as you have already given me a lot of your time. If you know why the higher totals might be out, then great, but otherwise, don't worry mate. This is the actual query I used on my DB (just in case you can spot any typos:


;WITH Entries(theValue, Items)
AS (
        SELECT          theValue,
                        SUM(Items) AS Items
        FROM            (
                                SELECT  u.theValue,
                                        1 AS Items
                                FROM    tbl_dir_details AS t1
                                UNPIVOT (
                                                theValue
                                                FOR theCol IN (t1.Category, t1.Category2, t1.Category3)
                                        ) AS u
 
                                UNION ALL
 
                                SELECT  ID,
                                        0
                                FROM    tbl_dir_categories
                        ) AS d
        GROUP BY        theValue
), Yak (ID, Category, PID, Items)
AS (
        SELECT          t1.ID,
                        t1.Category,
                        t1.PID,
                        e.Items
        FROM            tbl_dir_categories AS t1
        INNER JOIN      Entries AS e ON e.theValue = t1.ID
        LEFT JOIN       tbl_dir_categories AS x ON x.PID = t1.ID
        WHERE           x.PID IS NULL
 
        UNION ALL
 
        SELECT          t1.ID,
                        t1.Category,
                        t1.PID,
                        y.Items + e.Items
        FROM            tbl_dir_categories AS t1
        INNER JOIN      Entries AS e ON e.theValue = t1.ID
        INNER JOIN      Yak AS y ON y.PID = t1.ID
), Ctrl (ID, PID, Pth)
AS (
        SELECT  ID,
                PID,
                CAST(ID AS VARCHAR(MAX)) AS Pth
        FROM    tbl_dir_categories
        WHERE   ID = 2
 
        UNION ALL
 
        SELECT          t1.ID,
                        t1.PID,
                        c.Pth + '>' + CAST(t1.ID AS VARCHAR(MAX))
        FROM            tbl_dir_categories AS t1
        INNER JOIN      Ctrl AS c ON c.ID = t1.PID
)
 
SELECT          y.ID,
                y.Category,
                SUM(y.Items) AS Total
FROM            Yak AS y
INNER JOIN      Ctrl AS c ON c.ID = y.ID
GROUP BY        y.ID,
                y.Category,
                c.Pth
ORDER BY        c.Pth

Cheers

Lee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources