?
Solved

SQL Groupby

Posted on 2011-04-25
15
Medium Priority
?
157 Views
Last Modified: 2012-05-11
Hello
I have the below query which is providing me the correct output but i want the output in a proper format sich i am attaching in two sheets of the excel .

Please help

TIA Format.xlsx
select 
IM.inventorycontrolid,IM.Name1,EM.espid,EM.serialnumber,CM.companyname,IM.model,IM.quantityinstore
,CLM.caliberationdate,CLM.expirydate,CLM.certificatenumber,CM1.companyname,CLM.Remarks
from dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5

Open in new window

0
Comment
Question by:ExpertHelp79
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35460798

select 
IM.Name1,IM.inventorycontrolid,EM.espid,EM.serialnumber,CM.companyname,IM.model,sum(IM.quantityinstore)
,CLM.caliberationdate,CLM.expirydate,CLM.certificatenumber,CM1.companyname,CLM.Remarks
from dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5
group by IM.Name1, IM.inventorycontrolid, EM.espid, EM.serialnumber, CM.companyname, IM.model, CLM.caliberationdate,CLM.expirydate, CLM.certificatenumber,CM1.companyname,CLM.Remarks

Open in new window

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 35461159
same result as the CURRENT... Ineed the format as REQUIRED
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35464733
Which type of format, Cell color or Text alignment format?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:ExpertHelp79
ID: 35464893
i coloured the column just to indicate the change ...

Column A and Column F need to be changed

Column A: merge the recurring items and Column F add the number for recurring items in Column A and then Merge . I need the same output as the REQUIRED sheet
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35466191
Hi,

you cannot do this with a query. The "GROUP BY" clause exists to aggregate some rows of data and give you the result. What you can do is: you can create a group sum for each name ("Test1" - "Test5") with:
SELECT IM.Name1,SUM(IM.quantityinstore) AS SumOfQty
FROM dbo.inventorycontrol_master IM
WHERE IM.categoryid = 5
GROUP BY IM.Name1

Open in new window


This would give you the result of your two yellow columns. But as these are aggregates (sum in this case) you lost any detail information (which you normally don't need here, as it is a sum).

What you also can do is: You can create a detail list and attach an additional column with the aggregated sum for each line like this:

WITH qryNameSums AS
(
SELECT IM.Name1 AS Name2,SUM(IM.quantityinstore) AS SumOfQty
FROM dbo.inventorycontrol_master IM
WHERE IM.categoryid = 5
GROUP BY IM.Name1
)
SELECT	IM.inventorycontrolid,IM.Name1,
		EM.espid,EM.serialnumber,
		CM.companyname,IM.model,
		IM.quantityinstore,CLM.caliberationdate,
		CLM.expirydate,CLM.certificatenumber,
		CM1.companyname,CLM.Remarks,
		(SELECT SumOfQty FROM qryNameSums WHERE qryNameSums.Name2 = IM.Name1) AS SumOfNameQty
FROM	dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5

Open in new window


But you will of course get the sum in every row of the result.

You can NOT change any format inside of Excel with the query, it is not possible to use SQL to merge cells or color cells and so on. If you want this result you would need additional VBA which loops through the result table and changes the format of the cells of one group to merged.

Cheers,

Christian
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35466959
Y'all GROUP BY is being misused in your queries!!!!

What is needed is an ORDER BY clause!  GROUP BY is used with aggregations!

See attached code:
SELECT IM.inventorycontrolid
  ,IM.Name1
  ,EM.espid
  ,EM.serialnumber
  ,CM.companyname
  ,IM.model
  ,IM.quantityinstore
  ,CLM.caliberationdate
  ,CLM.expirydate
  ,CLM.certificatenumber
  ,CM1.companyname
  ,CLM.Remarks
FROM dbo.inventorycontrol_master IM
LEFT OUTER JOIN dbo.equipment_master EM
ON  IM.inventorycontrolid = EM.inventorycontrolid
LEFT OUTER JOIN dbo.company_master CM
ON  IM.manufacturerid = CM.companyid
LEFT OUTER JOIN dbo.caliberationrecord_master CLM
ON  EM.equipmentid = CLM.equipmentid
LEFT OUTER JOIN dbo.company_master CM1
ON  CLM.vendorid = CM1.companyid
WHERE IM.categoryid = 5
ORDER BY IM.Name1, EM.espid;

Open in new window

0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35467034
@8080_Diver:
OK, and how do you get the sum of the names groups "Test1" to "Test5" without using SUM (which is an aggregate) and GROUP BY? Show me how to do this with ORDER BY...:-)

Cheers,

Christian
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35467069
@Christian,

I see no aggregation in the author's query.

I have the below query which is providing me the correct output but i want the output in a proper format sich i am attaching in two sheets of the excel .
select 
IM.inventorycontrolid,IM.Name1,EM.espid,EM.serialnumber,CM.companyname,IM.model,IM.quantityinstore
,CLM.caliberationdate,CLM.expirydate,CLM.certificatenumber,CM1.companyname,CLM.Remarks
from dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5

Open in new window

0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35467205
OK, but you wrote "Y'all ...in your queries" and I read "Y'all" as "you all" - correct me, if I'm wrong. Moreover the author also posted an Excel file which shows which result is expected, so where could an "ORDER BY" help in this case?...

Cheers,

Christian
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 35468409
Bitsqueezer:
 
Hi,
your query is returning the result but how to merge the duplicate rows in column Name1 and sumofnameqty
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 35468960
Hi,

if your output table looks exactly like the one you posted in your Excel file above you can merge the cells with this little macro:

Public Sub MergeResults()
    Dim objRng As Excel.Range
    Dim objCell As Excel.Range
    Dim i As Long, k As Long
    Dim varCurrentValue As Variant
    Dim varLastValue As Variant
    
    Application.DisplayAlerts = False
    Set objRng = ActiveSheet.UsedRange
    varLastValue = ""
    For i = 1 To objRng.Rows.Count - 1
        varCurrentValue = objRng.Cells(1 + i, 1)
        If varCurrentValue <> varLastValue Then
            If varLastValue <> "" Then
                'Debug.Print "Merge " & k & "," & i - 1 & " " & varLastValue & "," & varCurrentValue
                With objRng.Range(Cells(k + 1, 1), Cells(i, 1))
                    .MergeCells = True
                    .VerticalAlignment = xlCenter
                End With
                With objRng.Range(Cells(k + 1, 6), Cells(i, 6))
                    .MergeCells = True
                    .VerticalAlignment = xlCenter
                End With
            End If
            varLastValue = varCurrentValue
            k = i
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Open in new window


Copy it to a standard module and create a button or something like that to start it - or start it directly with ALT-F8 and choose "MergeResults" from the list.

Cheers,

Christian
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 35469196
Christian
Thanks for the help... but i need to bind it to a gridview in asp.net
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35469529
Hi,

in ASP.NET you could manipulate a HTML table so you have a lot more to do as there is no merge command like in Excel. As you can create nested tables with HTML you could create one table with the group query (the first in my examples above) so you have three columns, one for the names, one empty and one for the sum. In the end you can fill each of the empty cells with the detail rows in x new tables which you insert into these cells.
I don't know if it is possible to create vertical merged cells in a grid view like Excel or HTML can, but I would guess it can't.

I'm afraid I cannot help you with a script in ASP.NET, for this you could maybe create a new thread, I can only help you with SQL Server questions or Office programming.

Cheers,

Christian
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question