How to summarize? - 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)

How to summarize?

Author

Topic

Plyft
Starting Member

8 Posts

Posted - 2008-02-18 : 09:51:45

My query looks like this…

SELECT c.customer_type as KUNDTYP,DATENAME(month,i.date_invoiced) as DATUM,DATEPART(yy,i.date_invoiced) as YEAR,
CATEGORY =

CASE
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 2)= 1 THEN 'RADIO'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 5571) = 1 THEN 'TV'
WHEN DBO.FNISCHILD(VIN.GROUP_ID,5)= 1 THEN 'DATA'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 6)= 1 THEN 'KOMPONENTER'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 1)= 1 THEN 'BACKUP'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 7)= 1 THEN 'DIGITAL'
ELSE 'OTHER'
END,

SUM(ir.QTY_ORDERED*ir.PRICE_1) as PRICE,

PROFIT = CASE
WHEN ir.kickback_cost > '0' THEN (SUM(ir.QTY_ORDERED*ir.PRICE_1)) –
(SUM(ir.qty_ordered*ir.kickback_cost))
ELSE (SUM(ir.QTY_ORDERED*ir.PRICE_1)) - (SUM(ir.qty_ordered*ir.actual_cost))
END

FROM invoice_row as ir

right JOIN V_INVENTORY AS VIN ON vin.product_id = ir.product_id
JOIN inventory as inv on ir.product_id = inv.product_id
JOIN inventory_flag as inf ON inf.product_id = ir.product_id
JOIN invoice as i ON ir.invoice_seq_id = i.invoice_seq_id
JOIN customer as c ON i.customer_id = c.customer_id

WHERE
INF.LICENS_PRODUCT = '0'
AND c.customer_type IN ('AA')
AND I.DATE_INVOICED BETWEEN '20071101' AND '20071130'

Group by c.customer_type, VIN.GROUP_ID, i.date_invoiced, ir.KICKBACK_COST

Order by Category

Reslut looks like this…

KUNDTYP DATUM YEAR CATEGORY PRICE PROFIT
AA November 2007 RADIO 340.90 10.90
AA November 2007 RADIO 92.09 10.36
AA November 2007 DATA 686.55 67.55
AA November 2007 DIGITAL 233.63 20.03
AA November 2007 DIGITAL 83.22 9.35
AA November 2007 DIGITAL 159.62 17.95
AA November 2007 DIGITAL 145.40 16.35
AA November 2007 DIGITAL 174.46 19.60
AA November 2007 DIGITAL 436.15 49.00
AA November 2007 DIGITAL 936.39 105.24
AA November 2007 DIGITAL 15502.34 1742.94
AA November 2007 DIGITAL 245.62 27.62
AA November 2007 DIGITAL 230.58 -5.88
AA November 2007 OTHER 0.00 0.00
AA November 2007 OTHER 0.00 0.00
AA November 2007 OTHER 999.00 535.00
AA November 2007 OTHER 1358.08 134.08
AA November 2007 KOMPONENTER 122.49 21.49
AA November 2007 KOMPONENTER 388.82 68.22

I want it to look like this (summarized )
KUNDTYP DATUM YEAR CATEGORY PRICE PROFIT
AA November 2007 RADIO 432.99 21.26
AA November 2007 DATA 686.55 67.55
AA November 2007 DIGITAL 18147.41 1982.17
AA November 2007 OTHER 2357.08 669.08
AA November 2007 KOMPONENTER 511.31 89.71

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 10:10:46

[code]Select KUNDTYP, Datum, [Year], CATEGORY, Sum(Price) as Price, Sum(Profit) as Profit
From
(
SELECT
c.customer_type as KUNDTYP,
DATENAME(month,i.date_invoiced) as DATUM,
DATEPART(yy,i.date_invoiced) as [YEAR],
CATEGORY =
CASE
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 2)= 1 THEN 'RADIO'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 5571) = 1 THEN 'TV'
WHEN DBO.FNISCHILD(VIN.GROUP_ID,5)= 1 THEN 'DATA'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 6)= 1 THEN 'KOMPONENTER'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 1)= 1 THEN 'BACKUP'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 7)= 1 THEN 'DIGITAL'
ELSE 'OTHER'
END,
(ir.QTY_ORDERED*ir.PRICE_1) as PRICE,
PROFIT =
CASE
WHEN ir.kickback_cost > '0' THEN (ir.QTY_ORDERED*ir.PRICE_1) –(ir.qty_ordered*ir.kickback_cost)
ELSE (ir.QTY_ORDERED*ir.PRICE_1) - (ir.qty_ordered*ir.actual_cost)
END
FROM invoice_row as ir
right JOIN V_INVENTORY AS VIN ON vin.product_id = ir.product_id
JOIN inventory as inv on ir.product_id = inv.product_id
JOIN inventory_flag as inf ON inf.product_id = ir.product_id
JOIN invoice as i ON ir.invoice_seq_id = i.invoice_seq_id
JOIN customer as c ON i.customer_id = c.customer_id
WHERE
INF.LICENS_PRODUCT = '0'
AND c.customer_type IN ('AA')
AND I.DATE_INVOICED BETWEEN '20071101' AND '20071130'
) t
Group by KUNDTYP, Datum, [Year], CATEGORY
Order by CATEGORY[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Plyft
Starting Member

8 Posts

Posted - 2008-02-18 : 10:36:01

Im on my knees and says a BIG thank you! It worked excellent!

Plyft
Starting Member

8 Posts

Posted - 2008-02-19 : 05:35:33

Do i dear to ask for one more favor? If i can i would like to know how to get all CATEGORYs listed in the result regardless if there is any sales or not. At this time its just leavs thoose categorys out.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 05:44:04

[code]Select KUNDTYP, Datum, [Year], CATEGORY, Sum(Price) as Price, Sum(Profit) as Profit
From
(
SELECT
c.customer_type as KUNDTYP,
DATENAME(month,i.date_invoiced) as DATUM,
DATEPART(yy,i.date_invoiced) as [YEAR],
CATEGORY =
CASE
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 2)= 1 THEN 'RADIO'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 5571) = 1 THEN 'TV'
WHEN DBO.FNISCHILD(VIN.GROUP_ID,5)= 1 THEN 'DATA'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 6)= 1 THEN 'KOMPONENTER'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 1)= 1 THEN 'BACKUP'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 7)= 1 THEN 'DIGITAL'
ELSE 'OTHER'
END,
(ir.QTY_ORDERED*ir.PRICE_1) as PRICE,
PROFIT =
CASE
WHEN ir.kickback_cost > '0' THEN (ir.QTY_ORDERED*ir.PRICE_1) –(ir.qty_ordered*ir.kickback_cost)
ELSE (ir.QTY_ORDERED*ir.PRICE_1) - (ir.qty_ordered*ir.actual_cost)
END
FROM V_INVENTORY AS VIN
LEFT JOIN invoice_row as ir ON vin.product_id = ir.product_id
JOIN inventory as inv on ir.product_id = inv.product_id
JOIN inventory_flag as inf ON inf.product_id = ir.product_id
JOIN invoice as i ON ir.invoice_seq_id = i.invoice_seq_id
JOIN customer as c ON i.customer_id = c.customer_id
WHERE
INF.LICENS_PRODUCT = '0'
AND c.customer_type IN ('AA')
AND I.DATE_INVOICED BETWEEN '20071101' AND '20071130'
) t
Group by KUNDTYP, Datum, [Year], CATEGORY
Order by CATEGORY[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Plyft
Starting Member

8 Posts

Posted - 2008-02-19 : 07:25:19

Sad to say but that didnt do the trick :(

Plyft
Starting Member

8 Posts

Posted - 2008-02-24 : 08:57:42

Can someone tell me how to get all CATEGORYs listed in the result regardless if there is any sales or not. At this time its just leavs thoose categorys out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-24 : 12:00:43

quote:
Originally posted by Plyft

Can someone tell me how to get all CATEGORYs listed in the result regardless if there is any sales or not. At this time its just leavs thoose categorys out.

change like this & try:-

Select KUNDTYP, Datum, [Year], CATEGORY, Sum(Price) as Price, Sum(Profit) as Profit
From
(
SELECT
c.customer_type as KUNDTYP,
DATENAME(month,i.date_invoiced) as DATUM,
DATEPART(yy,i.date_invoiced) as [YEAR],
CATEGORY =
CASE
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 2)= 1 THEN 'RADIO'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 5571) = 1 THEN 'TV'
WHEN DBO.FNISCHILD(VIN.GROUP_ID,5)= 1 THEN 'DATA'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 6)= 1 THEN 'KOMPONENTER'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 1)= 1 THEN 'BACKUP'
WHEN DBO.FNISCHILD(VIN.GROUP_ID, 7)= 1 THEN 'DIGITAL'
ELSE 'OTHER'
END,
(ir.QTY_ORDERED*ir.PRICE_1) as PRICE,
PROFIT =
CASE
WHEN ir.kickback_cost > '0' THEN (ir.QTY_ORDERED*ir.PRICE_1) –(ir.qty_ordered*ir.kickback_cost)
ELSE (ir.QTY_ORDERED*ir.PRICE_1) - (ir.qty_ordered*ir.actual_cost)
END
FROM V_INVENTORY AS VIN
LEFT JOIN invoice_row as ir ON vin.product_id = ir.product_id
LEFT JOIN inventory as inv on ir.product_id = inv.product_id
LEFT JOIN inventory_flag as inf ON inf.product_id = ir.product_id
LEFT JOIN invoice as i ON ir.invoice_seq_id = i.invoice_seq_id
LEFT JOIN customer as c ON i.customer_id = c.customer_id
WHERE
(INF.LICENS_PRODUCT = '0' OR inf.LICENS_PRODUCT IS NULL)
AND (c.customer_type IN ('AA') OR c.customer_type IS NULL)
AND (I.DATE_INVOICED BETWEEN '20071101' AND '20071130' OR i.DATE_INVOICED IS NULL)
) t
Group by KUNDTYP, Datum, [Year], CATEGORY
Order by CATEGORY

Plyft
Starting Member

8 Posts

Posted - 2008-02-24 : 17:14:57

Nah it didnt solve it.

Isnt there a way to just fake hit on every category?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 03:04:23

Can you provide some sample data from tables with their structures?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources