show total and details - 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)

show total and details

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-08 : 02:23:53

Hi

I have a categories table and a products table, the products are associated with a category, and my current select statement looks like this..

SELECT     COUNT(dbo.tbl_Categories.CatName) AS TotQty, dbo.tbl_Categories.CatName
FROM         dbo.tbl_Products INNER JOIN
                      dbo.tbl_Categories ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID
GROUP BY dbo.tbl_Categories.CatName

this give me ..

TotQty CatName
1 Books
2 DVD

I wonder how I can change the select statement so I retrieve a result like this..

TotQty CatName Description
1 Books Oliver Twist
2 DVD Dire Straits
2 DVD Elvis

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-08 : 02:58:31

[code]SELECT COUNT(dbo.tbl_Categories.CatName) AS TotQty, dbo.tbl_Categories.CatName, dbo.tbl_Products.Description
FROM dbo.tbl_Products INNER JOIN
dbo.tbl_Categories ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID
GROUP BY dbo.tbl_Categories.CatName, dbo.tbl_Products.Description[/code]This isn't exactly what you specified but your specification doesn't really make sense. If the total dvd-count is 2 then there is only one Dire Straits dvd and one Elvis dvd. Do you really want it to display 2 of each when there is actually only one??

--
Lumbago

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-08 : 03:05:35

Hi

Yes I think so, I need to display a result of a search for all categories and want to display a category header that also display the total of items for each category, Thanks for the help

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-08 : 03:17:12

Seemed that I where a little quick to reply, I now have this...

SELECT COUNT(dbo.tbl_Categories.CatName) AS TotQty, dbo.tbl_Categories.CatName, dbo.tbl_Products.Description, dbo.tbl_Products.Picture, dbo.tbl_Products.Name
FROM dbo.tbl_Products INNER JOIN
dbo.tbl_Categories ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID
GROUP BY dbo.tbl_Categories.CatName, dbo.tbl_Products.Description, dbo.tbl_Products.Picture, dbo.tbl_Products.Name

But that does not display the totalQty the way I want it. Now it only display Qty 1 for each DVD, I would like to display 2 (that is the total Qty for DVD's)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-08 : 03:34:26

I guess you could do something like this:

SELECT 
	cnt.TotQty, 
	dbo.tbl_Categories.CatName, 
	dbo.tbl_Products.Description, 
	dbo.tbl_Products.Picture, 
	dbo.tbl_Products.Name
FROM dbo.tbl_Products 
	INNER JOIN dbo.tbl_Categories 
		ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID
	INNER JOIN 
		(SELECT COUNT(b.CatName) AS TotQty, b.CatName,
		FROM dbo.tbl_Products a
			INNER JOIN dbo.tbl_Categories b
				ON a.CatID = dbo.b.ID
		GROUP BY b.CatName) AS cnt
	ON cnt.CatName = dbo.tbl_Categories.CatName

Doesn't make much sense to me but it should be to your requirements

--
Lumbago

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-08 : 03:46:31

Hi again

I get a incorrect syntax near FROM when I run the query... I dont see where it is an error do you?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-08 : 03:52:34

sorry...small typo:

	INNER JOIN 
		(SELECT COUNT(b.CatName) AS TotQty, b.CatName,<-- just remove this comma
		FROM dbo.tbl_Products a

--
Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 03:59:29

Use the new windowed functions available in SQL Server 2005.

SELECT		COUNT(*) OVER (PARTITION BY p.CatID) AS TotQty,
		c.CatName,
		p.Description
FROM		dbo.tbl_Products AS p
INNER JOIN	dbo.tbl_Categories AS c ON c.ID = p.CatID

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-08 : 04:42:44

Just what I needed, Thanks!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-08 : 04:45:46

Awesome...I *really* need to look more into these new functions.

--
Lumbago

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-08 : 05:12:55

One more question....
I can change the code like this...
SELECT COUNT(*) OVER (PARTITION BY p.CatID) AS TotQty, c.CatName, p.Description, p.name, p.picture, p.CatID
FROM dbo.tbl_Products AS p LEFT OUTER JOIN
dbo.tbl_Categories AS c ON c.ID = p.CatID
WHERE (p.Description LIKE '%Elvis%')

Then I get 1 hit in DVD's, is it also possible to retrive the ones the ones that doesn' match. Like this..

TotQty CatName Description Name Picture CatID
1 DVD blabla bla blabla 1
0 Books 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 05:31:54

Try this

SELECT		COUNT(p.CatID) OVER (PARTITION BY c.ID) AS TotQty,
		c.CatName,
		p.Description,
		p.Name,
		p.Picture,
		p.CatID
FROM		dbo.tbl_Categories AS c
LEFT JOIN	dbo.tbl_Products AS p ON p.CatID = c.ID
			AND p.Description LIKE '%Elvis%'

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-08 : 05:58:55

Excellent, Thank you very much!

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-09 : 05:35:17

I'm stuck again with kinda simular question..

I now have 1 table that looks like this..

NodeID Int
ParentNodeID Int
Name nVarchar
description nVarchar

NodeID ParentNodeID Name Description
1 Office supply
2 1 Businesscards
3 1 Envelopes
4 2 Card 1 text a
5 2 Card 2 text b
6 3 Envelope 1 text c
7 1 Other

My Question is, how can I the total qty for each category (in this case businesscards and envelopes) and the description..

like this..

Businesscards 2 items

Card 1
Card 2

Envelopes 1 item

Envelope 1

Other 0 item

Is this possible

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 05:41:32

Yes. Use a recursive CTE.

Also read this how to post proper sample data for us to work with
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-09 : 05:58:35

Hi Peso

I'm not sure what you mean.... But here's the code for the table creation..

CREATE TABLE [dbo].[Products](
[NodeID] [int] IDENTITY(1,1) NOT NULL,
[ParentNodeID] [int] NULL,
[Name] [nvarchar](50) NULL,
[ArtNo] [nvarchar](50) NULL,
[Description] [nvarchar](255) NULL,
[Price] [decimal](18, 2) NULL,
[Picture] [nvarchar](50) NULL,
[Lang] [nvarchar](50) NULL,
[NavigateUrl] [nvarchar](50) NULL,
[LinksOrder] [int] NULL
) ON [PRIMARY]

INSERT INTO Products (Name) VALUES ('Office supply')
INSERT INTO Products (Name, ParentNodeID) VALUES ('Businesscards', 1)
INSERT INTO Products (Name, ParentNodeID) VALUES ('Envelopes', 1)

INSERT INTO Products (Name, ParentNodeID) VALUES ('Card 1', 2)
INSERT INTO Products (Name, ParentNodeID) VALUES ('Card 2', 2)
INSERT INTO Products (Name, ParentNodeID) VALUES ('Envelope 1', 3)
INSERT INTO Products (Name, ParentNodeID) VALUES ('Other', 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 06:10:24

Also see
http://weblogs.sqlteam.com/peterl/archive/2007/10/04/Sum-up-a-tree-hierachy-in-SQL-Server-2005.aspx

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-09 : 06:48:20

Now I'm a little lost should I done something different with the code above?

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-09 : 07:04:49

I'm sorry but my knowledge in this matter is limited and I could really use some help showing me how I can do this....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 07:29:15

[code]DECLARE @Sample TABLE (NodeID INT, ParentNodeID INT, Name VARCHAR(50))

INSERT @Sample
SELECT 1, NULL, 'Office supply' UNION ALL
SELECT 2, 1, 'Businesscards' UNION ALL
SELECT 3, 1, 'Envelopes' UNION ALL
SELECT 4, 2, 'Card 1' UNION ALL
SELECT 5, 2, 'Card 2' UNION ALL
SELECT 6, 3, 'Envelope 1' UNION ALL
SELECT 7, 1, 'Other'

;WITH Yak (NodeID, ParentNodeID, Name, Items)
AS (
SELECT s.NodeID,
s.ParentNodeID,
s.Name,
1
FROM @Sample AS s
LEFT JOIN @Sample AS x ON x.ParentNodeID = s.NodeID
WHERE x.NodeID IS NULL

UNION ALL

SELECT s.NodeID,
s.ParentNodeID,
s.Name,
1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.ParentNodeID = s.NodeID
)

SELECT NodeID,
Name,
SUM(Items)
FROM Yak
GROUP BY NodeID,
Name[/code]

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-04-09 : 08:33:21

Hi Peso

Is it possible to tweak the code to that it only count categories that have any item. For example the "Other" category is displayed with 1, but there are actually no items under that category. It would be prefereble to have it like this instead..

NodeID Name TotQty

1 Office supply
2 Businesscards 2
4 Card 1
5 Card 2
3 Envelopes 1
6 Envelope 1
7 Other 0

I really appreciate your help since you obviously have great skills in Sql server, hope you can help me out if it even is possible to create this kind of result...

Best Regards

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources